[SQL] serial column vs. explicit sequence question
I'm having a problem and there seems to be 2 solutions. It is simple and straighforward, but will take several paragraphs to explain. I have a schema with a master-detail design. The master table does not have an expicit id, so I have a column of type serial. Lets say I need to insert a row into the master table and N rows into the detail table. After inserting a row into master, and before detail, I need to read the master table to obtain the value of the id for the row just inserted, so I can insert this id as the foreign key value for the N rows in the detail table. This seems like a poor solution because I have to write and then read the master table each time. With lot of activity on these tables, I don't know how well this will scale. Additionally, the only way that I can guarantee that I am getting the id of the most recent row inserted into master is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because otherwise, if other processes are inserting rows into master/detail concurrently, I may pick up the id from an incorrect row (the technique used to get the correct id is to include a timestamp column on the insert into master and then query for the latest row). A better solution would seem to use a sequence explicitly, rather than a id column of type serial. I would obtain the id value from the sequence, and then insert this id into the master table and into the detail table. This way, I wouldn't be writing/reading the same table constantly -- I would only be writing to it, and, I would guarantee that I would be using the correct id in both master and detail without have to SET TRANSACTION ISOLATION LEVEL SERIALIZEABLE. Any comments on which solution you would choose, or is there a better solution ? Thanks, Charlie ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] serial column vs. explicit sequence question
If session A and session B are concurrently doing the same master-detail transaction, wouldn't currval possibly reflect the sequence value used by the other session ? Or are you saying that since this will be an explicit transaction that currval won't reflect the fact that the sequence may have been incremented by another session ? On Thursday 13 June 2002 02:06 pm, Stephan Szabo wrote: > On Thu, 13 Jun 2002, Charlie Toohey wrote: > > I'm having a problem and there seems to be 2 solutions. It is simple and > > straighforward, but will take several paragraphs to explain. > > > > I have a schema with a master-detail design. The master table does not > > have an expicit id, so I have a column of type serial. > > > > Lets say I need to insert a row into the master table and N rows into the > > detail table. After inserting a row into master, and before detail, I > > need to read the master table to obtain the value of the id for the row > > just inserted, so I can insert this id as the foreign key value for the N > > rows in the detail table. > > > > This seems like a poor solution because I have to write and then read the > > master table each time. With lot of activity on these tables, I don't > > know how well this will scale. Additionally, the only way that I can > > guarantee that I am getting the id of the most recent row inserted into > > master is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because > > otherwise, if other processes are inserting rows into master/detail > > concurrently, I may pick up the id from an incorrect row (the technique > > used to get the correct id is to include a timestamp column on the insert > > into master and then query for the latest row). > > > > A better solution would seem to use a sequence explicitly, rather than a > > id column of type serial. I would obtain the id value from the sequence, > > and then insert this id into the master table and into the detail table. > > This way, I wouldn't be writing/reading the same table constantly -- I > > would only be writing to it, and, I would guarantee that I would be using > > the correct id in both master and detail without have to SET TRANSACTION > > ISOLATION LEVEL SERIALIZEABLE. > > > > Any comments on which solution you would choose, or is there a better > > solution ? > > Well, serial really is just an integer with a default value pulling from a > sequence, so right now you can use currval on the sequence (which I think > gets named something like __seq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] serial column vs. explicit sequence question
that definitely helps ! thank you Jason --- the key thing that I didn't
undertand, and you have now enlightened me, is that currval was connection
dependent --- I didn't think this would be guaranteed to work with concurrent
transactions, but now I understand.
Just prior to receiving your message, I posted a reply basically asking how
currval would work if there were concurrent updates --- please ignore that
response.
thanks everyone --- I now feel "empowered" to carry on with my project
On Thursday 13 June 2002 03:01 pm, Jason Earl wrote:
> Charlie Toohey <[EMAIL PROTECTED]> writes:
> > I'm having a problem and there seems to be 2 solutions. It is simple
> > and straighforward, but will take several paragraphs to explain.
> >
> > I have a schema with a master-detail design. The master table does
> > not have an expicit id, so I have a column of type serial.
> >
> > Lets say I need to insert a row into the master table and N rows
> > into the detail table. After inserting a row into master, and before
> > detail, I need to read the master table to obtain the value of the
> > id for the row just inserted, so I can insert this id as the foreign
> > key value for the N rows in the detail table.
> >
> > This seems like a poor solution because I have to write and then
> > read the master table each time. With lot of activity on these
> > tables, I don't know how well this will scale. Additionally, the
> > only way that I can guarantee that I am getting the id of the most
> > recent row inserted into master is to SET TRANSACTION ISOLATION
> > LEVEL SERIALIZABLE --- because otherwise, if other processes are
> > inserting rows into master/detail concurrently, I may pick up the id
> > from an incorrect row (the technique used to get the correct id is
> > to include a timestamp column on the insert into master and then
> > query for the latest row).
> >
> > A better solution would seem to use a sequence explicitly, rather
> > than a id column of type serial. I would obtain the id value from
> > the sequence, and then insert this id into the master table and into
> > the detail table. This way, I wouldn't be writing/reading the same
> > table constantly -- I would only be writing to it, and, I would
> > guarantee that I would be using the correct id in both master and
> > detail without have to SET TRANSACTION ISOLATION LEVEL
> > SERIALIZEABLE.
> >
> > Any comments on which solution you would choose, or is there a
> > better solution ?
> >
> > Thanks,
> > Charlie
>
> The SERIAL type is a thin veneer over an underlying conglomeration of
> a unique index and a sequence, nothing more, nothing less. I still
> prefer to use the old syntax that spelled this out explicitly (mostly
> because it reminded me that I needed to drop the sequences as well as
> the table if I made changes during the development phases of my
> project). Instead of using a serial type I have a whole pile of
> scripts that contain bits that look like this:
>
> DROP TABLE prod_journal;
> DROP SEQUENCE prod_journal_id_seq;
>
> CREATE SEQUENCE prod_journal_id_seq;
>
> CREATE TABLE prod_journal (
>id int PRIMARY KEY
> DEFAULT nextval('prod_journal_id_seq'),
>...
> );
>
> The SERIAL type does precisely the same sort of thing. The only
> difference is that PostgreSQL thinks up the sequence name for you
> (currently PostgreSQL tries to choose a name that looks precisely like
> the one I have chosen in this example). The reason that I bring this
> up is A) it makes me happy to think that I have been using PostgreSQL
> long enough that my PostgreSQL memories predate the SERIAL type, and
> B) to point out that there is not really a difference between using
> the SERIAL type and using sequences explicitly.
>
> What you *really* need is to get acquainted with the nifty sequence
> functions currval and nextval. They hold the secret to sequence
> Nirvana. See Chapter 4 Section 11 of the PostgreSQL User's Guide for
> the full scoop. The short story is that curval gives the current
> value of the sequence (for whichever backend you are connected to) and
> nextval will give you the next value of the sequence.
>
> Now let's say that you had two simple tables foo for the master record
> and bar for the detail records.
>
> test=# create table foo (id serial primary key, name text);
> NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL
> column 'foo.id' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
> index 'foo_p
[SQL] schema-qualified permission problem
Why can't my primary user (ttvuser) access tables (owned by owneruser) for
which they've been granted access? I can describe the table, but can't
read it.
===
Here's what I'm getting:
ttvdev=> \c - owneruser
Password:
You are now connected as new user owneruser.
ttvdev=> \d
List of relations
Schema | Name | Type | Owner
++--+
owneruser | users | table| owneruser
ttvdev=> select count(*) from owneruser.users;
count
---
0
(1 row)
ttvdev=> \dp
Access privileges for database "ttvdev"
Schema | Table |Access privileges
++-
owneruser | users | {=,owneruser=arwdRxt,ttvuser=arwd}
ttvdev=> \c - postgres
Password:
You are now connected as new user postgres.
ttvdev=# select count(*) from owneruser.users;
count
---
0
(1 row)
ttvdev=> \c - ttvuser
Password:
You are now connected as new user ttvuser.
ttvdev=> \d owneruser.users
Table "owneruser.users"
Column |Type | Modifiers
+-+---
user_id| integer | not null
initials | character varying(3)| not null
username | character varying(18) | not null
password | character varying(25) | not null
email | character varying(256) | not null
authenticationdate | timestamp without time zone |
creationdate | timestamp without time zone |
modifydate | timestamp without time zone |
userlastmodified | timestamp without time zone |
adminlastmodified | timestamp without time zone |
autologin | character varying(1)|
active | character varying(1)|
passhint | character varying(25) |
firstname | character varying(40) |
lastname | character varying(40) |
sex| character varying(6)|
department | character varying(40) |
manager_flag | character varying(1)|
phone | character varying(50) |
Indexes: pk_users primary key btree (user_id)
ttvdev=> select count(*) from owneruser.users;
ERROR: owneruser: permission denied
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
