[GENERAL] Re: sequences
Perhaps I was too brief before, wanting to spare folks long quotes from the manual
that we all have, but here goes (quoting from
http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createsequence.htm):
"After a sequence is created, you may use the function nextval(seqname) to get a new
number from the sequence. The function currval('seqname') may be used to determine the
number returned by the last call to nextval(seqname) for the specified sequence _in
the current session_". [emphasis added]
"Unexpected results may be obtained if a cache setting greater than one is used for a
sequence object that will be used
concurrently by multiple backends. Each backend will allocate and cache successive
sequence values during one access to
the sequence object and increase the sequence object's last_value accordingly. Then,
the next cache-1 uses of nextval within
that backend simply return the preallocated values without touching the shared
object. So, numbers allocated but not used in
the current session will be lost. __Furthermore, although multiple backends are
guaranteed to allocate distinct sequence values,
the values may be generated out of sequence when all the backends are considered.__"
[again, emphasis added].
Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
http://www.eudoramail.com
Re: [GENERAL] Re: sequences
Actually, it looks like currval is defined to give the
value last used in your session. So, the second case
(not in transaction) should still always give the
value of the last nextval, assuming they're part of
the same session.
Stephan Szabo
[EMAIL PROTECTED]
On Thu, 21 Sep 2000, Edward Q. Bridges wrote:
> actually they're saying two different things :)
>
> first, to explain my example a bit better:
>
> the difference between this:
>
> > > begin;
> > > insert into foo (A,B) values (B);
> > > select currval('foo_A_seq');
> > > commit;
>
> and this:
>
> > > insert into foo (A,B) values (B);
> > > select currval('foo_A_seq');
>
> is that the first is considered (by the rdbms) to be
> one "atomic" transaction; the second is considered to
> be two.
>
> the rdbms processes one transaction at a time, in no
> guaranteed order (basically). so, in theory, there is
> a possibility that an insert by another user to table
> foo could occur after your insert and before your select
> off the sequence. the implication being, you would get
> a value for A that would not refer to the row you just
> inserted. by grouping the sql statements into a single
> transaction, you ensure the rdbms will process them in
> the order you specify.
>
> the other statement you quote from the docs (which is not
> entirely clear to me without context) seems to refer to
> the fact that a sequence will never return the same number
> twice when nextval('seq_name') is called.
>
> HTH
> --e--
>
>
> On Wed, 20 Sep 2000 23:13:23 -0700, K Parker wrote:
>
> > Edward Q. Bridges's detailed statement regarding sequences, of which I extract
>merely the
> most pessimistic part:
> >
> > > begin;
> > > insert into foo (A,B) values (B);
> > > select currval('foo_A_seq');
> > > commit;
> > >
> > > note that the transaction is key, without
> > > which there's no guarantee that some other
> > > statement will affect the value of the
> > > sequence.
> >
> > quite clearly conflicts what what seems to me to be the plain meaning of the
>manual page for
> CREATE SEQUENCE which states, in part:
> >
> > > multiple backends are guaranteed to
> > > allocate distinct sequence values
> >
> > Can some knowledgable person here save a bunch of us plain old user-programmers
>the trouble
> of trying to trace down what the source says and just clarify this issue? Thanks!
> > >
> >
> >
> > Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
> http://www.eudoramail.com
> >
>
>
>
Re: [GENERAL] replication
On Thu, 21 Sep 2000, Daryl Chance wrote: > Could this possibly be done using triggers? I'm new to > postgres, but I know on a project I was doing using oracle > the dba could setup triggers to run on the OnInsert() (not > sure what it's actually called in oracle...). Do maybe > on the "OnInsert" of table foo you could do: > > Insert into foo@remotesite1 > > Is this possible in postgres? I'm looking at using postgres > for the next version of my SW and if replication isn't in, > I'm gonna need something like this :). You could probably write a C trigger that would propogate changes, except that there are still problems. What do you do when you roll back the transaction? Currently, there aren't triggers for transaction start and end. Triggers that do stuff outside the database right now are a bad idea unless you have some other mechanism to determine whether something was really supposed to be done. It could be done, but isn't trivial.
Re: [GENERAL] perl Pg module and result status
On Wed, 20 Sep 2000 21:24:17 -0400, Neil Conway wrote: > I believe he is using straight Pg - the perl interface to Postgres. > AFAIK, that's independant of DBI::Pg, which is the Postgres > driver for DBI. > ahhh! "i see" said the blind man :) > I agree with you, however: I've found DBD::Pg quite a bit easier > to work with than Pg. It also gives you the possibility of > moving your code to another database with relatively small > changes. > yes, i agree totally. it also makes using a new database a lot easier, since you're just calling the same functions. > If you don't mind me commenting on your code: not at all! > > $user = ''; > > $pass = ''; > > $dburl = ''; # should be: dbi:Pg:dbname=[your database name] > Is there a reason you're not using constants? e.g. > > use constant DB_USER => 'foo'; > use constant DB_PASSWD => 'qwerty'; > nope. didn't occur to me for this sort of example. also seems to bring in extra overhead. is there an advantage? > You might also want to add the "$DBI::errstr" to the error message > on a connection failure. > yeah, i used it on the other method calls. should have put it there as well. i think, tho, that the driver will by default print out that error string when it dies. regards --e--
Re: [GENERAL] Re: sequences
actually they're saying two different things :)
first, to explain my example a bit better:
the difference between this:
> > begin;
> > insert into foo (A,B) values (B);
> > select currval('foo_A_seq');
> > commit;
and this:
> > insert into foo (A,B) values (B);
> > select currval('foo_A_seq');
is that the first is considered (by the rdbms) to be
one "atomic" transaction; the second is considered to
be two.
the rdbms processes one transaction at a time, in no
guaranteed order (basically). so, in theory, there is
a possibility that an insert by another user to table
foo could occur after your insert and before your select
off the sequence. the implication being, you would get
a value for A that would not refer to the row you just
inserted. by grouping the sql statements into a single
transaction, you ensure the rdbms will process them in
the order you specify.
the other statement you quote from the docs (which is not
entirely clear to me without context) seems to refer to
the fact that a sequence will never return the same number
twice when nextval('seq_name') is called.
HTH
--e--
On Wed, 20 Sep 2000 23:13:23 -0700, K Parker wrote:
> Edward Q. Bridges's detailed statement regarding sequences, of which I extract
>merely the
most pessimistic part:
>
> > begin;
> > insert into foo (A,B) values (B);
> > select currval('foo_A_seq');
> > commit;
> >
> > note that the transaction is key, without
> > which there's no guarantee that some other
> > statement will affect the value of the
> > sequence.
>
> quite clearly conflicts what what seems to me to be the plain meaning of the manual
>page for
CREATE SEQUENCE which states, in part:
>
> > multiple backends are guaranteed to
> > allocate distinct sequence values
>
> Can some knowledgable person here save a bunch of us plain old user-programmers the
>trouble
of trying to trace down what the source says and just clarify this issue? Thanks!
> >
>
>
> Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
http://www.eudoramail.com
>
Re: [GENERAL] Re: Large Objects
in effect, this turns the filesystem into a "poor-mans" balanced tree. the rdbms gives you a "rich-mans" balanced tree, but along with the overhead of the rdbms. cheers --e-- On Thu, 21 Sep 2000 15:20:39 +0300, Alessio Bragadini wrote: > Neil Conway wrote: > > > > a BLOB. Conversely, Unix filesystems store directories as unsorted > > > lists, which are a lot slower to search than the database's > > > structured indexes. > > > Wow, can anyone confirm this (with Postgres preferrably)? In talking > > with some developers at my old job, they all agreed that storing large > > pieces of data (1k < x < 16K) was significantly faster on the FS than > > I believe he's talking about storing all files in the same directory, > which is simply The Wrong Way for a number of reasons. While saving a > large number of external files, we use a sub-dir structure in the form > /data/f4/d3/12/myfile.bin in order to spread the number of files in a > tree pseudorandomly. This is the same approach used by the Squid > webcache. > > -- > Alessio F. Bragadini [EMAIL PROTECTED] > APL Financial Serviceshttp://village.albourne.com > Nicosia, Cyprus phone: +357-2-755750 > > "It is more complicated than you think" > -- The Eighth Networking Truth from RFC 1925 >
Re: [GENERAL] replication
Could this possibly be done using triggers? I'm new to postgres, but I know on a project I was doing using oracle the dba could setup triggers to run on the OnInsert() (not sure what it's actually called in oracle...). Do maybe on the "OnInsert" of table foo you could do: Insert into foo@remotesite1 Is this possible in postgres? I'm looking at using postgres for the next version of my SW and if replication isn't in, I'm gonna need something like this :). btw, remotesite could be setup in what oracle referred to as "tnsnames.ora". It was a file that had a list of hosts, ports and the database name so that you wouldn't have to know all that info to connect to an oracle database, just what you named it, your username and your password. Thanks, | Daryl Chance | I have made this letter longer then | | Valuedata, LLC | usual because I lacked the time to | | Memphis, TN| make it shorter. -- Blaise Pascal | - Original Message - From: "Adam Lang" <[EMAIL PROTECTED]> To: "PGSQL General" <[EMAIL PROTECTED]> Sent: Thursday, September 21, 2000 7:52 AM Subject: [GENERAL] replication > Are there any type of replication features in postgresql 7.0? > > I would like it where two databases have the same structure, but say at > midnight every night Database 1 synchs up database 2. > > Granted, I could always write code to do that, but it wouldn't be very > sophisticated. (If I coded it, would do something like find rows in table > 1 which aren't in the second database, append them database two, same with > the second table, etc.) > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company > >
Re: [GENERAL] Public Database of zip code information
i believe that you can get that info from www.allstats.com. we get our zipcode info from them quarterly. mikeo At 05:03 PM 9/20/00 -0700, Adam Haberlach wrote: > Sometime in the past year, someone mentioned a database that >contained zipcode, lat/long, and city information--does anyone else >remember this, or should I check into a drug clinic? > > I'm pretty much looking for a way to correlate zip, city, >and/or geographic locations. > >-- >Adam Haberlach| A billion hours ago, human life appeared on >[EMAIL PROTECTED] | earth. A billion minutes ago, Christianity >http://www.newsnipple.com | emerged. A billion Coca-Colas ago was >'88 EX500 | yesterday morning. -1996 Coca-Cola Ann. Rpt. >
Re: [GENERAL] replication
Adam Lang wrote: > > Are there any type of replication features in postgresql 7.0? > > I would like it where two databases have the same structure, but say at > midnight every night Database 1 synchs up database 2. > > Granted, I could always write code to do that, but it wouldn't be very > sophisticated. (If I coded it, would do something like find rows in table > 1 which aren't in the second database, append them database two, same with > the second table, etc.) And you would also have to check which records have been modified and replicate them. You are not the first person to ask for this feature and it is on the TODO list: http://www.postgresql.org/docs/pgsql/doc/TODO.detail/replication but it is categorized under "exotic features", so I don't know when we'll see it implemented :( But I think www.psql.com are working on replication right now. Poul L. Christiansen > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company
Re: [GENERAL] replication
On Thu, 21 Sep 2000, Adam Lang wrote: > Are there any type of replication features in postgresql 7.0? Not exist some standard solution for PG for DB replication ... Maybe in a far future (via some WAL logs?). Karel
[GENERAL] replication
Are there any type of replication features in postgresql 7.0? I would like it where two databases have the same structure, but say at midnight every night Database 1 synchs up database 2. Granted, I could always write code to do that, but it wouldn't be very sophisticated. (If I coded it, would do something like find rows in table 1 which aren't in the second database, append them database two, same with the second table, etc.) Adam Lang Systems Engineer Rutgers Casualty Insurance Company
Re: [GENERAL] Re: Large Objects
Neil Conway wrote: > > a BLOB. Conversely, Unix filesystems store directories as unsorted > > lists, which are a lot slower to search than the database's > > structured indexes. > Wow, can anyone confirm this (with Postgres preferrably)? In talking > with some developers at my old job, they all agreed that storing large > pieces of data (1k < x < 16K) was significantly faster on the FS than I believe he's talking about storing all files in the same directory, which is simply The Wrong Way for a number of reasons. While saving a large number of external files, we use a sub-dir structure in the form /data/f4/d3/12/myfile.bin in order to spread the number of files in a tree pseudorandomly. This is the same approach used by the Squid webcache. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Resolved! (was: Re[8]: [GENERAL] WTF is going on with PG_VERSION?)
Well, thanks to everybody who helped! It was indeed the problem with opening files - the limit was set to 1024 with more than 100 possible backends... Well, I suppose it wouldn't hurt to change the error message in the future versions of Postgres, 'cause now it is somewhat... misleading... ;-> Greetings, The Hermit Hacker! At 21.09.2000, 13:34, you wrote: >> Well, last question then: I wasn't too specific, but the problem >> with this crash is that not ONE SINGLE backend fails, but ALL OF >> THEM AT ONCE: someone comes running to me and shouts 'our site is >> down!', when I login and type 'ps eax | grep postgres' there >> are no postgres processes in memory... Which is strange, as I >> connect to Postgres from PHP, and use `persistent` connections, so >> the backends which are in memory should have already read their >> PG_VERSIONs... >> Is it as it should be with ENFILE failure? THH> that is as it was when we were hitting it ... we are actually running a db THH> on 4 seperate ports, and we would see one db beign down and the rest THH> running happily along ... as soon as one db goes for that last slot and THH> can't find it, that one would completely shut down, as its the 'parent THH> process' that appears to be the one going for it ... -- Yours, Alexey V. Borzov
Re: Re[6]: [GENERAL] WTF is going on with PG_VERSION?
On Thu, 21 Sep 2000, Alexey Borzov wrote: > Greetings, Tom! > > At 20.09.2000, 10:41, you wrote: > > TL> "Alexey V. Borzov" <[EMAIL PROTECTED]> writes: > >> Nope, that's not the problem. I just checked and every DB has its own > >> PG_VERSION. Besides, _all_ of the databases are accessed on regular > >> basis (I'm speaking of a website), but the crashes occur only once in > >> a while (like, once a week)... > TL> I'm wondering if you could be running out of kernel filetable slots, > TL> so that the open of PG_VERSION is failing with ENFILE. (This would be > TL> the trouble spot just because it's the first file a new backend tries > TL> to open, and being a new backend it has no possible recovery tactic > TL> like closing other files. Once a backend is up and running it can > TL> usually survive ENFILE open failures by closing off other files.) > > This MIGHT be problem. I'm not sure, as it wasn't me who compiled > the kernel for the box, but I'll look into it... > > Well, last question then: I wasn't too specific, but the problem > with this crash is that not ONE SINGLE backend fails, but ALL OF > THEM AT ONCE: someone comes running to me and shouts 'our site is > down!', when I login and type 'ps eax | grep postgres' there > are no postgres processes in memory... Which is strange, as I > connect to Postgres from PHP, and use `persistent` connections, so > the backends which are in memory should have already read their > PG_VERSIONs... > Is it as it should be with ENFILE failure? that is as it was when we were hitting it ... we are actually running a db on 4 seperate ports, and we would see one db beign down and the rest running happily along ... as soon as one db goes for that last slot and can't find it, that one would completely shut down, as its the 'parent process' that appears to be the one going for it ...
