pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEdit multiple rows concurrent save
3+ messages / 2 participants
[nested] [flat]
* Edit multiple rows concurrent save
@ 2011-09-29 21:58 Péter Szabó <matyee.nmi@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Péter Szabó @ 2011-09-29 21:58 UTC (permalink / raw)
To: pgsql-sql
Hi there!
I am trying to solve a problem, but I am totally stucked. I have a
database with users, cards (260) and decks. The users can have cards
(a lot of), their cards can be in different decks or in trade state. I
don't want to store the cards one by one, so one user has only one row
for a specific card type, and this row indicates the total number of
cards what the user have, and how many card has in decks and how many
in trade state.
Table for users:
Table "public.users"
Column | Type |
--------------------+-----------------------------+
id | bigint |
name | character varying(20) |
Table for cards:
Table "public.cards"
Column | Type |
-----------------+-----------------------+
id | integer |
name | character varying(50) |
Table for connect users to cards:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
total, decks, auctions are the filed which are storing the card
numbers which the user have.
Every user can have several deck assembled. Every card can belong to
multiple decks independently.
Table for decks and for the cards in the decks:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
Table "public.decks_has_cards"
Column | Type | Modifiers
---------+----------+-----------
deck_id | bigint | not null
card_id | integer | not null
piece | smallint |
Of course decks_has_cards.card_id is a foreign key from
users_has_cards. When a user assembling a deck, he can use from his
available card. Available card count from a specific type is counted
from users_has_cards.total - users_has_cards.auctions. The user picks
cards from available cards, and then save the deck.
users_has_cards.decks stores the biggest number of cards which the
user has in one of his decks. With this it can be determined how many
cards of the user can be put to trade state.
The task is to hold this in a consistent state and to grant
users_has_cards.auctions + users_has_cards.decks never can be higher
then users_has_cards.total. It should be also granted that
users_has_cards.total - users_has_cards.auctions number of cards from
a specific type can be in any decks.
The deck assembly input comes from web, and parsed with PHP, so it is
absolutely not trusted.
I am started to write a PL/PgSQL function to handle the save of a
deck, but it seems unable to solve this issue. I don't know how to go
forward, so any idea is appreciated.
Matyee
^ permalink raw reply [nested|flat] 3+ messages in thread
* Edit multiple rows concurrent save
@ 2011-09-29 22:07 Péter Szabó <matyee.nmi@gmail.com>
parent: Péter Szabó <matyee.nmi@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Péter Szabó @ 2011-09-29 22:07 UTC (permalink / raw)
To: pgsql-sql
Hi there!
I am trying to solve a problem, but I am totally stucked. I have a
database with users, cards (260) and decks. The users can have cards
(a lot of), their cards can be in different decks or in trade state. I
don't want to store the cards one by one, so one user has only one row
for a specific card type, and this row indicates the total number of
cards what the user have, and how many card has in decks and how many
in trade state.
Table for users:
Table "public.users"
Column | Type |
--------------------+-----------------------------+
id | bigint |
name | character varying(20) |
Table for cards:
Table "public.cards"
Column | Type |
-----------------+-----------------------+
id | integer |
name | character varying(50) |
Table for connect users to cards:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
total, decks, auctions are the filed which are storing the card
numbers which the user have.
Every user can have several deck assembled. Every card can belong to
multiple decks independently.
Table for decks and for the cards in the decks:
Table "public.users_has_cards"
Column | Type |
----------+---------+
user_id | bigint |
card_id | integer |
total | integer |
decks | integer |
auctions | integer |
Table "public.decks_has_cards"
Column | Type | Modifiers
---------+----------+-----------
deck_id | bigint | not null
card_id | integer | not null
piece | smallint |
Of course decks_has_cards.card_id is a foreign key from
users_has_cards. When a user assembling a deck, he can use from his
available card. Available card count from a specific type is counted
from users_has_cards.total - users_has_cards.auctions. The user picks
cards from available cards, and then save the deck.
users_has_cards.decks stores the biggest number of cards which the
user has in one of his decks. With this it can be determined how many
cards of the user can be put to trade state.
The task is to hold this in a consistent state and to grant
users_has_cards.auctions + users_has_cards.decks never can be higher
then users_has_cards.total. It should be also granted that
users_has_cards.total - users_has_cards.auctions number of cards from
a specific type can be in any decks.
The deck assembly input comes from web, and parsed with PHP, so it is
absolutely not trusted.
I am started to write a PL/PgSQL function to handle the save of a
deck, but it seems unable to solve this issue. I don't know how to go
forward, so any idea is appreciated.
Matyee
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Edit multiple rows concurrent save
@ 2011-10-08 09:19 Jasen Betts <jasen@xnet.co.nz>
parent: Péter Szabó <matyee.nmi@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2011-10-08 09:19 UTC (permalink / raw)
To: pgsql-sql
On 2011-09-29, Péter Szabó <matyee.nmi@gmail.com> wrote:
> users_has_cards.auctions + users_has_cards.decks never can be higher
> then users_has_cards.total. It should be also granted that
> users_has_cards.total - users_has_cards.auctions number of cards from
> a specific type can be in any decks.
>
> The deck assembly input comes from web, and parsed with PHP, so it is
> absolutely not trusted.
>
> I am started to write a PL/PgSQL function to handle the save of a
> deck, but it seems unable to solve this issue. I don't know how to go
> forward, so any idea is appreciated.
so users_has_cards has a check ( total >= decks + auctions )
and decks_has_cards needs triggers on insert, update, and delete that
manipulate the decks column of the users_has_cards record that
corresponds to that card.
This is probably goiung to make updates to the decks_has_cards table about
10 times slower (should still be faster than you could have done in PHP)
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-10-08 09:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-29 21:58 Edit multiple rows concurrent save Péter Szabó <matyee.nmi@gmail.com>
2011-09-29 22:07 ` Péter Szabó <matyee.nmi@gmail.com>
2011-10-08 09:19 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox