Re: [GENERAL] Sequences / Replication

Jonathan Eastgate wrote: > We're seeing some odd behaviour from a PostgreSQL group - one running as > primary and the other as a > hot slave using streaming replication. > > When a failover event occurs and we switch to the hot slave as primary > sequences in tables jump by 33 > - so where the l

Re: [GENERAL] Sequences / Replication

From: "Jonathan Eastgate" To: "PostgreSql-general" Sent: Thursday, 20 October, 2016 03:10:53 Subject: Re: [GENERAL] Sequences / Replication And further to my last post - another post in the forums related to this: https://devon.so/2015/02/06/as-tale-of-sequences-and-postgres

Re: [GENERAL] Sequences / Replication

And further to my last post - another post in the forums related to this: https://devon.so/2015/02/06/as-tale-of-sequences-and-postgresql-replication-9/ Thanks. *Jonathan J. Eastgate* Chief Technology Officer | simPRO Software Group Ph: 1300 139 467+61 7 3147 8777

[GENERAL] Sequences / Replication

Hi everyone. We're seeing some odd behaviour from a PostgreSQL group - one running as primary and the other as a hot slave using streaming replication. When a failover event occurs and we switch to the hot slave as primary sequences in tables jump by 33 - so where the last number allocated in the

Re: [GENERAL] Sequences, txids, and serial order of transactions

On Sat, Jun 11, 2016 at 9:03 PM, Christian Ohler wrote: > we have a use case similar to auditing packages like pgMemento or Audit > Trigger 91plus – we are looking to keep an ordered history of certain write > transactions. I'm trying to understand the trade-offs between different > ways of gett

Re: [GENERAL] Sequences, txids, and serial order of transactions

Christian Ohler writes: > we have a use case similar to auditing packages like pgMemento or Audit > Trigger 91plus – we are looking to keep an ordered history of certain write > transactions. I'm trying to understand the trade-offs between different > ways of getting that order, i.e., assigning

Re: [GENERAL] Sequences, txids, and serial order of transactions

> On 12 Jun 2016, at 4:03, Christian Ohler wrote: > we have a use case similar to auditing packages like pgMemento or Audit > Trigger 91plus – we are looking to keep an ordered history of certain write > transactions. I'm trying to understand the trade-offs between different ways > of gettin

[GENERAL] Sequences, txids, and serial order of transactions

Hi, we have a use case similar to auditing packages like pgMemento or Audit Trigger 91plus – we are looking to keep an ordered history of certain write transactions. I'm trying to understand the trade-offs between different ways of getting that order, i.e., assigning numbers to transactions (idea

Re: [GENERAL] Sequences not created, bug in pg_dump?

On 03/20/2015 10:21 AM, "Leonardo M. Ramé" wrote: El 19/03/15 a las 14:13, Adrian Klaver escibió: On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote: El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: Hi, I'm creating a database dump excluding

Re: [GENERAL] Sequences not created, bug in pg_dump?

El 19/03/15 a las 14:13, Adrian Klaver escibió: On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote: El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created impl

Re: [GENERAL] Sequences not created, bug in pg_dump?

On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote: El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I resto

Re: [GENERAL] Sequences not created, bug in pg_dump?

El 19/03/15 a las 13:09, Adrian Klaver escibió: On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T tabl

Re: [GENERAL] Sequences not created, bug in pg_dump?

On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related ob

[GENERAL] Sequences not created, bug in pg_dump?

Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpl

Re: [GENERAL] Sequences not moved to new tablespace

On Tue, Feb 24, 2015 at 10:32:42AM -0500, Tom Lane wrote: > For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the > case where the database already has some tables that have been explicitly > placed into that tablespace. (I forget the exact reason for this, but > it's got something

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: If you want to move a whole database to a different tablespace (the only reason I can think of for doing what you are trying to so), use the command ALTER DATABASE ... SET TABLESPACE ... >>> Thanks Laurenz. I tried your suggestion: >>> >>> psql -U postg

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:39 GMT-05:00 Albe Laurenz : > Guillaume Drolet wrote: > >> If you want to move a whole database to a different tablespace (the > only reason > >> I can think of for doing what you are trying to so), use the command > >> ALTER DATABASE ... SET TABLESPACE ... > > > Thanks Laurenz. I tr

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:18 GMT-05:00 Adrian Klaver : > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: > >> >> >> > >> >> So how did all this stuff get into the non-default tablespace in the >> first place? >> >> >> Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files >> mentionned in m

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:32 GMT-05:00 Tom Lane : > Adrian Klaver writes: > > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: > >> That makes sense. I will give it a try. Thanks. > > > Alright, now I am thoroughly confused:) I thought this is how you to > > this point, using the above commands to move from t

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: >> If you want to move a whole database to a different tablespace (the only >> reason >> I can think of for doing what you are trying to so), use the command >> ALTER DATABASE ... SET TABLESPACE ... > Thanks Laurenz. I tried your suggestion: > > psql -U postgres -c "ALTER

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 07:32 AM, Tom Lane wrote: Adrian Klaver writes: On 02/24/2015 07:10 AM, Guillaume Drolet wrote: That makes sense. I will give it a try. Thanks. Alright, now I am thoroughly confused:) I thought this is how you to this point, using the above commands to move from the non-defaul

Re: [GENERAL] Sequences not moved to new tablespace

Adrian Klaver writes: > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: >> That makes sense. I will give it a try. Thanks. > Alright, now I am thoroughly confused:) I thought this is how you to > this point, using the above commands to move from the non-default > tablespace back to the default

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 07:10 AM, Guillaume Drolet wrote: So how did all this stuff get into the non-default tablespace in the first place? Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files mentionned in my previous post. The answer would seem to be just reverse whate

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:06 GMT-05:00 Adrian Klaver : > On 02/24/2015 05:06 AM, Guillaume Drolet wrote: > >> >> >> 2015-02-24 7:07 GMT-05:00 Guillaume Drolet > >: >> >> >> >> 2015-02-23 14:14 GMT-05:00 Adrian Klaver > >: >> >> >>

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 8:45 GMT-05:00 Albe Laurenz : > Guillaume Drolet wrote: > > Digging a little more, I found that not only sequences were not moved > but also many tables in > > pg_catalog are still in my old tablespace. This is expected since the > query in the SQL files I used to > > move the tables an

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 05:06 AM, Guillaume Drolet wrote: 2015-02-24 7:07 GMT-05:00 Guillaume Drolet mailto:droletguilla...@gmail.com>>: 2015-02-23 14:14 GMT-05:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 02/23/2015 10:08 AM, Guillaume Drolet wrote: Hello,

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: > Digging a little more, I found that not only sequences were not moved but > also many tables in > pg_catalog are still in my old tablespace. This is expected since the query > in the SQL files I used to > move the tables and indexes had a WHERE clause like this: > > SEL

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 7:07 GMT-05:00 Guillaume Drolet : > > > 2015-02-23 14:14 GMT-05:00 Adrian Klaver : > > On 02/23/2015 10:08 AM, Guillaume Drolet wrote: >> >>> Hello, >>> >>> I moved all my tables and indexes from one tablespace to pg_default using >>> >>> ALTER TABLE ... SET TABLESPACE pg_default; >>> A

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-23 14:14 GMT-05:00 Adrian Klaver : > On 02/23/2015 10:08 AM, Guillaume Drolet wrote: > >> Hello, >> >> I moved all my tables and indexes from one tablespace to pg_default using >> >> ALTER TABLE ... SET TABLESPACE pg_default; >> ALTER INDEX ... SET TABLESPACE pg_default; >> >> Some 2500 fi

Re: [GENERAL] Sequences not moved to new tablespace

On 02/23/2015 10:08 AM, Guillaume Drolet wrote: Hello, I moved all my tables and indexes from one tablespace to pg_default using ALTER TABLE ... SET TABLESPACE pg_default; ALTER INDEX ... SET TABLESPACE pg_default; Some 2500 files were moved to pg_default but 461 files remain in the tablespace

Re: [GENERAL] Sequences not moved to new tablespace

On 02/23/2015 10:08 AM, Guillaume Drolet wrote: Hello, I moved all my tables and indexes from one tablespace to pg_default using ALTER TABLE ... SET TABLESPACE pg_default; ALTER INDEX ... SET TABLESPACE pg_default; Some 2500 files were moved to pg_default but 461 files remain in the tablespace

Re: [GENERAL] Sequences not moved to new tablespace

On 02/23/2015 10:08 AM, Guillaume Drolet wrote: Hello, I moved all my tables and indexes from one tablespace to pg_default using ALTER TABLE ... SET TABLESPACE pg_default; ALTER INDEX ... SET TABLESPACE pg_default; Some 2500 files were moved to pg_default but 461 files remain in the tablespace

[GENERAL] Sequences not moved to new tablespace

Hello, I moved all my tables and indexes from one tablespace to pg_default using ALTER TABLE ... SET TABLESPACE pg_default; ALTER INDEX ... SET TABLESPACE pg_default; Some 2500 files were moved to pg_default but 461 files remain in the tablespace and so I cannot drop it. When I query, for examp

Re: [GENERAL] Sequences in foreign tables

On Tue, Sep 16, 2014 at 10:17 AM, Daniele Varrazzo wrote: > On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier > wrote: >> On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo >> wrote: > >>> I'm learning now something about foreign tables in PG 9.3. I wonder if >>> there is a clean way to use a sequ

Re: [GENERAL] Sequences in foreign tables

On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier wrote: > On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo > wrote: >> I'm learning now something about foreign tables in PG 9.3. I wonder if >> there is a clean way to use a sequence on the remote side, so that an >> "insert into remote_table val

Re: [GENERAL] Sequences in foreign tables

On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo wrote: > I'm learning now something about foreign tables in PG 9.3. I wonder if > there is a clean way to use a sequence on the remote side, so that an > "insert into remote_table values ([data not including id]) returning > id" would ask the remot

[GENERAL] Sequences in foreign tables

Hello, I'm learning now something about foreign tables in PG 9.3. I wonder if there is a clean way to use a sequence on the remote side, so that an "insert into remote_table values ([data not including id]) returning id" would ask the remote server to generate a new value for id. As it stands now

Re: [GENERAL] sequences and RULEs

On Fri, 2009-06-05 at 14:32 +0100, Richard Huxton wrote: > Rafal Pietrak wrote: > > The NEW tuple of the table EVENTLOG, in its ID field at the moment of > > RULE execution has a value of 5! But after everything is finished, the > > actual value deposited in that record is 4. > > A rule rewrites t

Re: [GENERAL] sequences and RULEs

Rafal Pietrak wrote: Now I'd like to write a RULE, that automatically updates references between EVENTLOG and STATUS: The NEW tuple of the table EVENTLOG, in its ID field at the moment of RULE execution has a value of 5! But after everything is finished, the actual value deposited in that reco

[GENERAL] sequences and RULEs

Hi All! I've seen sometning unexpected here. I'd apreciate it if someone could give me a hint of why this have happened and may be a sugesstion of a workaround. I'm writing "Building Access Control System" (BACS). My environment is "Debian testing" with "their current" postgresql version: 8.3.7.

Re: [GENERAL] Sequences change in a rolled-back transactions

Alvaro Herrera wrote: > Erwin Moller wrote: > >> I thought a transaction that is rolled back, rolls back *everything* >> done in that transaction. >> Appearantly sequences are not included. > > Yes. This is actually a desirable property, because it allows sequences > to work fine in concurrent

Re: [GENERAL] Sequences change in a rolled-back transactions

Alvaro Herrera schreef: Erwin Moller wrote: I thought a transaction that is rolled back, rolls back *everything* done in that transaction. Appearantly sequences are not included. Hi Alvaro, Thanks for your reply. Yes. This is actually a desirable property, because it allows

Re: [GENERAL] Sequences change in a rolled-back transactions

Erwin Moller wrote: > I thought a transaction that is rolled back, rolls back *everything* > done in that transaction. > Appearantly sequences are not included. Yes. This is actually a desirable property, because it allows sequences to work fine in concurrent scenarios (which are, after all, t

[GENERAL] Sequences change in a rolled-back transactions

Hi group, I just noticed getting the next number of a sequence doesn't respect a transaction. Here is an example: === erwin=# SELECT nextval('tblofferlabel_offerlabelid_seq'::regclass) as nextofferlabelid; nextofferlabelid --

Re: [GENERAL] Sequences using libpqxx

On 2008-08-20 12:13, Roshni Mani wrote: > Does the sequence commands like nextval().setval(),curval() exist > using libpqxx? You just run an ordinary query: select nextval('sequence_name') Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just

[GENERAL] Sequences using libpqxx

Hi all, Does the sequence commands like nextval().setval(),curval() exist using libpqxx? Thanks and regards Roshni

Re: [GENERAL] Sequences

I want to create a sequence that increases in unit column 3 for each record individually in column 2 How do i create a sequence that can manage this? Is there a solution for this? Yeah, depesz shows how to do this here http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-s

[GENERAL] Sequences

Hi, I need to do something with a sequence. This is the example: CREATE TABLE TABLE1( PRIMARY1 INTEGER NOT NULL, OTHER1 VARCHAR (20) NOT NULL, CONSTRAINT "MYPK1" PRIMARY KEY (PRIMARY1) ); CREATE SEQUENCE "SEQ_1" INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CACHE 1 NO CYCLE

Re: [GENERAL] sequences and currval()

On Mon, Jun 02, 2008 at 11:55:14AM -0400, Michael P. Soulier wrote: > Hello, > > I'm migrating a db schema in an automated fashion, using this > > UPDATE clients_client > SET icp_id = null > WHERE icp_id = 1; > UPDATE icps_icp > SET id = nextval('public.icps_icp_id_seq') > WHERE i

[GENERAL] sequences and currval()

Hello, I'm migrating a db schema in an automated fashion, using this UPDATE clients_client SET icp_id = null WHERE icp_id = 1; UPDATE icps_icp SET id = nextval('public.icps_icp_id_seq') WHERE id = 1; UPDATE clients_client SET icp_id = currval('public.icps_icp_id_seq') WHE

Re: [GENERAL] sequences and dependences

On Fri, 23 May 2008, Laurent ROCHE wrote: I would like to write a request where I would get the table name and column name of a sequence. I have been looking at the sytem table and could not link the sequence from pg_class to a table (and even less to a table). There's an example of how to c

Re: [GENERAL] sequences and dependences

> I would like to write a request where I would get the table name and > column name of a sequence. > In fact I want the opposite of pg_get_serial_sequence ! You are looking for refobjid in pg_depend, but this time the sequence could be a dependent to more than table. A query like the one

[GENERAL] sequences and dependences

Hi, I would like to write a request where I would get the table name and column name of a sequence. In fact I want the opposite of pg_get_serial_sequence ! I have been looking at the sytem table and could not link the sequence from pg_class to a table (and even less to a table). And I have not

Re: [GENERAL] sequences vs oids as primary keys

On 7/25/06, Kenneth Downs <[EMAIL PROTECTED]> wrote: craigp wrote: >1) does it make sense (and would it be possible) to make a rule which would, >say, somehow write into the oid field of a tuple to be returned by lastoid? i'm >assuming here that the database would not have oid's enabled. > > > W

Re: [GENERAL] sequences vs oids as primary keys

craigp wrote: 1) does it make sense (and would it be possible) to make a rule which would, say, somehow write into the oid field of a tuple to be returned by lastoid? i'm assuming here that the database would not have oid's enabled. We do this in a trigger. We assign the NEXTVAL to a variab

Re: [GENERAL] sequences vs oids as primary keys

craigp wrote: i had some questions/thoughts on using sequences vs oids as pks... it's a common requirement to obtain the last value of a sequence after an insert. most other databases support such a feature, but since oid's are deprecated, and only oid's are returned, this isn't supported by post

[GENERAL] sequences vs oids as primary keys

i had some questions/thoughts on using sequences vs oids as pks... it's a common requirement to obtain the last value of a sequence after an insert. most other databases support such a feature, but since oid's are deprecated, and only oid's are returned, this isn't supported by postgres (and perfor

Re: [GENERAL] Sequences/defaults and pg_dump

On 2/7/06, Martijn van Oosterhout wrote: > On Tue, Feb 07, 2006 at 03:28:31PM +0300, Nikolay Samokhvalov wrote: > > The real situation would be as the following. > > I want to use some algorithm to hide real number of registered users > > in my table user. So, I don't want to use simple sequence,

Re: [GENERAL] Sequences/defaults and pg_dump

On Fri, Feb 10, 2006 at 07:34:35 -0500, Doug McNaught <[EMAIL PROTECTED]> wrote: > Bruno Wolff III <[EMAIL PROTECTED]> writes: > > Or, just start your sequence counting at 100. Or use bigint and > start it at a billion. That may work if you only have access to one id number, but you don't

Re: [GENERAL] Sequences/defaults and pg_dump

Bruno Wolff III <[EMAIL PROTECTED]> writes: > On Tue, Feb 07, 2006 at 15:28:31 +0300, > Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: >> The real situation would be as the following. >> I want to use some algorithm to hide real number of registered users >> in my table user. So, I don't want to

Re: [GENERAL] Sequences/defaults and pg_dump

On Tue, Feb 07, 2006 at 15:28:31 +0300, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > The real situation would be as the following. > I want to use some algorithm to hide real number of registered users > in my table user. So, I don't want to use simple sequence, when every > new registered us

Re: [GENERAL] Sequences/defaults and pg_dump

Joachim Wieland wrote: > On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote: > > Joachim Wieland wrote: > > > On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: > > > > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on > > > > > a serial column, but we h

Re: [GENERAL] Sequences/defaults and pg_dump

On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote: > Joachim Wieland wrote: > > On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: > > > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on > > > > a serial column, but we haven't gotten around to enforcing

Re: [GENERAL] Sequences/defaults and pg_dump

Joachim Wieland wrote: > On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: > > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on > > > a serial column, but we haven't gotten around to enforcing that yet. > > > TODO has: > > > * %Disallow changing default exp

Re: [GENERAL] Sequences/defaults and pg_dump

On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on > > a serial column, but we haven't gotten around to enforcing that yet. > TODO has: > * %Disallow changing default expression of a SERIAL column This sh

Re: [GENERAL] Sequences/defaults and pg_dump

There is no SERIAL type in the standard at all. Moreover, standard defines following expression for SEQUENCE GENERATORs: ::= NEXT VALUE FOR Postgres has non-standard equivalent - nextval()... So, sequences implementation in PostgreSQL isn't standard-compliant. On 2/7/06, John D. Burger <[EMAI

Re: [GENERAL] Sequences/defaults and pg_dump

"John D. Burger" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on >> a serial column, but we haven't gotten around to enforcing that yet. > Is this per the Standard? SERIAL isn't in the standard. > If so, then the oft-repeated

Re: [GENERAL] Sequences/defaults and pg_dump

Tom Lane wrote: The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on a serial column, but we haven't gotten around to enforcing that yet. Is this per the Standard? If so, then the oft-repeated mantra that SERIAL is simply a macro for an INTEGER column with a particular DEFA

Re: [GENERAL] Sequences/defaults and pg_dump

Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > Forget about SERIAL. I have INTEGER column with some expression as > DEFAULT in it. No, you have a SERIAL column that you've improperly mucked with the implementation of. If you'd declared it as INTEGER to start with, you could do whatever you wan

Re: [GENERAL] Sequences/defaults and pg_dump

Nikolay Samokhvalov wrote: > On 2/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: > > Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > > > testseq=# CREATE TABLE test(id SERIAL, data TEXT); > > > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for > > > serial column "test.id" > > > CR

Re: [GENERAL] Sequences/defaults and pg_dump

On 2/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: > Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > > testseq=# CREATE TABLE test(id SERIAL, data TEXT); > > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for > > serial column "test.id" > > CREATE TABLE > > *** > > ALTER TABLE test

Re: [GENERAL] Sequences/defaults and pg_dump

Tom Lane wrote: > Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > > testseq=# CREATE TABLE test(id SERIAL, data TEXT); > > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for > > serial column "test.id" > > CREATE TABLE > > *** > > ALTER TABLE test ALTER COLUMN id SET DEFAULT ne

Re: [GENERAL] Sequences/defaults and pg_dump

Nikolay Samokhvalov <[EMAIL PROTECTED]> writes: > testseq=# CREATE TABLE test(id SERIAL, data TEXT); > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for > serial column "test.id" > CREATE TABLE > *** > ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10; Th

Re: [GENERAL] Sequences/defaults and pg_dump

On Tue, Feb 07, 2006 at 03:28:31PM +0300, Nikolay Samokhvalov wrote: > The real situation would be as the following. > I want to use some algorithm to hide real number of registered users > in my table user. So, I don't want to use simple sequence, when every > new registered user in my system can

Re: [GENERAL] Sequences/defaults and pg_dump

On 2/7/06, Martijn van Oosterhout wrote: > On Tue, Feb 07, 2006 at 02:33:56PM +0300, Nikolay Samokhvalov wrote: > Well, it's a very contrived example (I can't think of a reason why one > would do that) but I agree it is a bug. You could acheive the same > effect by setting the step of the sequenc

Re: [GENERAL] Sequences/defaults and pg_dump

On Tue, Feb 07, 2006 at 02:33:56PM +0300, Nikolay Samokhvalov wrote: > Maybe it was discussed already, but I think it's very strange behavior > and things should be changed (please correct me if I'm wrong) > > Suppose we have database containing only one simple table: > So, if we don't know the

[GENERAL] Sequences/defaults and pg_dump

Maybe it was discussed already, but I think it's very strange behavior and things should be changed (please correct me if I'm wrong) Suppose we have database containing only one simple table: *** template1=# CREATE DATABASE testseq; template1=# \c testseq testseq=# CREATE TABLE test(id SERIAL, da

Re: [GENERAL] sequences not restoring properly

I need to amend what I said before; apparently, the 8.1.1 dump is creating correct sequence values (except in one instance), but it's changing the names to "match" the tables and columns they go with. Which is nice, I suppose, except that I use an object-relational mapping API that requires hard

Re: [GENERAL] sequences not restoring properly

Brian Dimeler wrote: Doing it that way doesn't produce any errors, but it still produces incorrect sequence names and values. Are these sequences that you created by hand and then associated with a column? Versus using serial/bigserial types? Sincerely, Joshua D. Drake Doug McNaught wrote:

Re: [GENERAL] sequences not restoring properly

Brian Dimeler <[EMAIL PROTECTED]> writes: > In the database dump however, this default is omitted (and consequently, when > restoring, the new > server creates an implicit sequence named 'bands_dbbandcode_seq' instead of > the correct > bands_dbcode_seq, and it is never set to the correct value

Re: [GENERAL] sequences not restoring properly

Doing it that way doesn't produce any errors, but it still produces incorrect sequence names and values. Doug McNaught wrote: Brian Dimeler <[EMAIL PROTECTED]> writes: I'm trying to transition a database from one server to another, the old one running Postgres 7.4.1 and the new, 8.1.1. When

Re: [GENERAL] sequences not restoring properly

Brian Dimeler <[EMAIL PROTECTED]> writes: > I'm trying to transition a database from one server to another, the > old one running Postgres 7.4.1 and the new, 8.1.1. When I try to > restore using a pg_dump plaintext sql file from a nightly backup via > the usual > > psql thedb < backup.sql The bes

[GENERAL] sequences not restoring properly

I'm trying to transition a database from one server to another, the old one running Postgres 7.4.1 and the new, 8.1.1. When I try to restore using a pg_dump plaintext sql file from a nightly backup via the usual psql thedb < backup.sql It works for the most part, but encounters several errors

Re: [GENERAL] sequences w/o holes

On 19.12.2005, at 12:14 Uhr, Marian Naghen wrote: Can anyones suggest some readings about implementing sequences w/o holes ? Question: why? I doubt that this is possible without heavy locking, which kills your performance. cug -- PharmaLine Essen, GERMANY and Big Nerd Ranch Europe - Pos

Re: [GENERAL] sequences w/o holes

On Dec 19, 2005, at 20:14 , Marian Naghen wrote: Can anyones suggest some readings about implementing sequences w/o holes ? Check the mailing list archives. In short, if you want to guarantee no holes, you don't use sequences. IIRC, the process is: 1. Set up another table (foo) that holds

Re: [GENERAL] sequences w/o holes

Marian Naghen schrieb: Can anyones suggest some readings about implementing sequences w/o holes ? not w/o a busload of race conditions and/or heavy locking. Usually you want to avoid this. So are you really sure you dont just want if for cosmetics? ---(end of broadcast)

[GENERAL] sequences w/o holes

Can anyones suggest some readings about implementing sequences w/o holes ?     thanx in advance.

Re: [GENERAL] sequences in transaction blocks

Okay, all clear. Thanx a lot. - Original Message - From: "Tino Wildenhain" <[EMAIL PROTECTED]> To: "Marian Naghen" <[EMAIL PROTECTED]> Cc: Sent: Monday, December 19, 2005 7:50 AM Subject: Re: [GENERAL] sequences in transaction blocks > Am Montag,

Re: [GENERAL] sequences in transaction blocks

Am Montag, den 19.12.2005, 09:39 + schrieb Marian Naghen: > I want to insert records inside a transaction block (BEGIN - > COMMIT/ROLLBACK). If the transaction fails, > the sequence do not rollback and retain the new value. > > This is the default behavior of sequences ? Yes it is (its the

Re: [GENERAL] sequences in transaction blocks

am 19.12.2005, um 9:39:11 - mailte Marian Naghen folgendes: > I want to insert records inside a transaction block (BEGIN - > COMMIT/ROLLBACK). If the transaction fails, > the sequence do not rollback and retain the new value. > > This is the default behavior of sequences ? Yes. HTH, An

[GENERAL] sequences in transaction blocks

I want to insert records inside a transaction block (BEGIN - COMMIT/ROLLBACK). If the transaction fails, the sequence do not rollback and retain the new value.   This is the default behavior of sequences ?     I use the following table:   CREATE TABLE proceduri(  procedura_id int4 NOT NUL

Re: [GENERAL] sequences, moving from 8.0.4 to 8.1 ?

On 10/21/05, Zlatko Matić <[EMAIL PROTECTED]> wrote: > I was reading release notes for 8.1 and there is something about changes > concerning replacing ::text with ::regclass in default clauses. There is a > query in Release notes, that should be executed to update dump from previos > versions. > B

[GENERAL] sequences, moving from 8.0.4 to 8.1 ?

Hello, I was reading release notes for 8.1 and there is something about changes concerning replacing ::text with ::regclass in default clauses. There is a query in Release notes, that should be executed to update dump from previos versions. But, even without it,  I see that all serial filed

Re: [GENERAL] Sequences question & problem

On Mon, May 16, 2005 at 12:29:27PM +0200, Lada 'Ray' Lostak wrote: > > I am having small trouble with sequences. I am inserting row into table, > and I need to get value of its 'id' column (sequencen type). As far I know > it have to be done by > > SELECT last_value FORM seq_name > >

Re: [GENERAL] Sequences question & problem

Thank you.. I didn't know about race condition.. Shame on me :) > If you're using PostgreSQL 8.0 or later then you can get the sequence > name with the pg_get_serial_sequence() function: Is there any way how to do that (or add this function) in 7.3.x ? Best regards, Lada 'Ray' Lostak Unreal64 Dev

[GENERAL] Sequences question & problem

Hi there, I am having small trouble with sequences. I am inserting row into table, and I need to get value of its 'id' column (sequencen type). As far I know it have to be done by SELECT last_value FORM seq_name (or next_val before insert). Normally, sequences are created by 't

Re: [GENERAL] sequences in schemas

On Tue, 31 Aug 2004 11:09:07 -0400, Joe Maldonado <[EMAIL PROTECTED]> wrote: >CREATE SCHEMA joe >CREATE SEQUENCE joe_seq start 1 >CREATE TABLE joe_table (int id, varchar name) >; > >and I get a syntax error for SEQUENCE. This will work in 8.0. http://www.postgresql.org/docs/7.4/static/sql

Re: [GENERAL] sequences in schemas

You forgot the ";" CREATE SCHEMA joe; CREATE SEQUENCE joe.joe_seq start 1; CREATE TABLE joe.joe_table (int id, varchar name); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

[GENERAL] sequences in schemas

Hello, How come within a create schema block I cannot create a sequence? I have entered in: CREATE SCHEMA joe CREATE SEQUENCE joe_seq start 1 CREATE TABLE joe_table (int id, varchar name) ; and I get a syntax error for SEQUENCE. though if it is just tables I do not -Joe --

[GENERAL] Sequences & rules

I created a table to hold some forum messages : table messages ( id serial primary key, parent_folder integer not null references folders(id), --in another table -- for replies, this stores the message which we reply

  1   2   >