Re: [GENERAL] Install from Source On Windows - University of Sydney Research
Craig Ringer, 24.08.2009 08:51: You'll probably want to initdb, edit the postgresql.conf created by initdb as desired, then start the postmaster manually using pg_ctl (making sure to point pg_ctl at the data directory you created). It's not hard. Yep, I have done that as well (to quickly set up development databases). The command would be: pg_ctl -s -D "\Path\To\Datadir" start If you want to automate it, just write a batch file. Remember to use ".cmd" not ".bat" so you run under the win32 cmd.exe script processor not the ancient dos emulation monster command.com . I don't think there is any difference between .cmd and .bat in any of the NT based Windows versions. At least on my WinXP (and earlier with W2K) double-clicking a .bat file always starts cmd.exe *not* command.com Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange "missing tables" problem
Hello Tom, Tom Lane a écrit : > Denis BUCHER writes: >> I really don't understand what's happening here ? > > "\dt customers" will show you the customers table that's visible > according to your search_path setting. Apparently schema "import" > is either not in your search path at all, or behind "rma". It is in the search path. Do you mean that \dt customers shows only the FIRST "customers" table found ? If yes, that's a part of the explanation, BUT : If I do \dt (without specifying a table name), does it lists only ONE table of each name, and only the one in the first schema present in the search path. Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange "missing tables" problem
Hello, Guillaume Lelarge a écrit : >> I have a strange problem since I moved some tables to a schema, some >> tables are missing from the list (with \d or \dt) but they are still >> present anyway ???! >> >> Example : >>> $ psql mybase >>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. >>> Saisissez: >>> mybase=# >>> bw_rma=# \dt >>> Liste des relations >>> Schéma | Nom| Type | Propriétaire >>> -+--+---+-- >>> import | rebates_products | table | postgres >>> import | rebates_customers| table | postgres >>> rma | categories | table | postgres >>> rma | customers| table | postgres >>> rma | defauts | table | postgres >>> rma | providers| table | postgres >> No trace of my import.clients table ? >> >> But if I do : >>> bw_rma=# SELECT count(*) FROM import.customers; >>> count >>> --- >>> 86703 >>> (1 ligne) >> My table is there and I can access it !!! >> >> Any hint or help would be greatly appreciated ! >> >> I can do without it but, it's a little strange not to be able to list >> the objects present in the database... >> > > \d does not show all the objects available in the database. If one is > available in schema A and in schema B, it will be displayed at most once, > depending on your search_path configuration. > > I suppose you have something like 'rma, import, ...' for search_path, so it > only displays rma.customers and not impor.customers. Yes that's correct. Therefore my "problem" is the "normal" behavior of \dt. But "normal" means "expected". But I don't find it very secure/handy, because you expect to see all your tables. Is there a way to change the behavior of \dt so that it lists ALL tables present in search path ? Thanks a lot for your help Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to create a multi-column index with 2 dates using 'gist'?
Hi, I am using 8.3 and pgAdmin III. I have a couple of tables using 2 DATE columns like 'startdate' and 'enddate' (just date, not interested in time in these columns). I have some queries (some using OVERLAPS) involving both 'startdate' and 'enddate' columns. I tried to create a multi column index using pgAdmin and it comes back with this error: ERROR: data type date has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. I search the pdf docs and online without finding what an "operator class" for DATE would be. Would a multi-column index help in that case (OVERLAPS and dates comparison) anyway? Or should I just define an index for each of the dates? Below are the table and index defintions. Thanks Fred - CREATE INDEX startenddate ON times USING gist (startdate, enddate); - -- Table: times -- DROP TABLE times; CREATE TABLE times ( id serial NOT NULL, startdate date NOT NULL, enddate date NOT NULL, starttime time without time zone, endtime time without time zone, CONSTRAINT pk_id PRIMARY KEY (id) ) WITH (OIDS=FALSE); ALTER TABLE times OWNER TO postgres; GRANT ALL ON TABLE times TO postgres; GRANT ALL ON TABLE times TO public;
Re: [GENERAL] Strange "missing tables" problem
Hi Denis, Le lundi 24 août 2009 à 10:21:33, Denis BUCHER a écrit : > > [...] > > I suppose you have something like 'rma, import, ...' for search_path, so > > it only displays rma.customers and not impor.customers. > > Yes that's correct. Therefore my "problem" is the "normal" behavior of > \dt. But "normal" means "expected". But I don't find it very > secure/handy, because you expect to see all your tables. Is there a way > to change the behavior of \dt so that it lists ALL tables present in > search path ? > No. But you can always take a look a this: http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/ You can probably do a \set dt your_query, and then :dt; instead of \dt. ... a few moments later... I tried and it works. Put this line in your .psqlrc file: \set dt '(SELECT n.nspname as \"Schéma\", c.relname as \"Nom\", CASE c.relkind WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'vue\' WHEN \'i\' THEN \'index\' WHEN \'S\' THEN \'séquence\' WHEN \'s\' THEN \'spécial\' END as \"Type\", r.rolname as \"Propriétaire\" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_cata log.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (\'r\',\'\') AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\') ORDER BY 1,2)' It should be one line only, and you can add more schemas on the n.nspname filter (information_schema for example). Now, I get this with this .psqlrc trick: guilla...@laptop:~$ psql -q a a=# set search_path to public, toto; a=# \dt Liste des relations Schéma | Nom | Type | Propriétaire +-+---+-- public | t1 | table | ab1 public | t2 | table | a2 public | t3 | table | postgres toto | t4 | table | guillaume (4 lignes) a=# :dt; Schéma | Nom | Type | Propriétaire +-+---+-- information_schema | sql_features| table | guillaume information_schema | sql_implementation_info | table | guillaume information_schema | sql_languages | table | guillaume information_schema | sql_packages| table | guillaume information_schema | sql_sizing | table | guillaume information_schema | sql_sizing_profiles | table | guillaume pgagent| pga_exception | table | guillaume pgagent| pga_job | table | guillaume pgagent| pga_jobagent| table | guillaume pgagent| pga_jobclass| table | guillaume pgagent| pga_joblog | table | guillaume pgagent| pga_jobstep | table | guillaume pgagent| pga_jobsteplog | table | guillaume pgagent| pga_schedule| table | guillaume public | t1 | table | ab1 public | t2 | table | a2 public | t3 | table | postgres toto | t1 | table | guillaume toto | t4 | table | guillaume (19 lignes) Hope it helps. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return field based on field= NULL or not
On 24 Aug 2009, at 7:50, Scott Marlowe wrote: On Sun, Aug 23, 2009 at 11:43 PM, Juan Backson wrote: Hi, Thank you for your help. What I want to dos is as follows: SELECT COALESCE(fieldA::text,fieldB||fieldC||fieldD) from ring where group_id = 1 if fieldB is NULL, i will want it to return fieldC|| fieldD if fieldB and fieldC is null, I want it to return fieldD. I get the impression you mean || to mean C-style OR instead of SQL- style concatenate? If not, Scott gave you the right solution already, otherwise read on. Basically, fieldD is always going to have data, but fieldB and fieldC can be NULL. How can I revise the query to meet that purpose? SELECT CASE WHEN fieldA IS NOT NULL THEN fieldA WHEN fieldB IS NOT NULL THEN fieldB WHEN fieldC IS NOT NULL THEN fieldC ELSE fieldD END FROM ring WHERE group_id = 1; Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a92716d11861465718119! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return field based on field= NULL or not
On Mon, Aug 24, 2009 at 12:54:31PM +0200, Alban Hertroys wrote: > CASE > WHEN fieldA IS NOT NULL THEN fieldA > WHEN fieldB IS NOT NULL THEN fieldB > WHEN fieldC IS NOT NULL THEN fieldC > ELSE fieldD > END BTW, the above expression is identical to: COALESCE(fieldA,fieldB,fieldC,fieldD) -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Mon, Aug 24, 2009 at 2:03 AM, Tom Lane wrote: > Jeff Ross writes: >> Tom Lane wrote: >>> heap_update is broken. Details left as an exercise for the reader > >> Well, as the reader that started this all ;-) should I be worried? >> Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just >> relax, don't worry and have a sparkling adult beverage? > > Well, it's a pretty bad bug but as far as I can see a simple "VACUUM > table" command should fix it up --- would you confirm? At the very least taking regular pg_dumps is probably wise. That's probably wise even if there aren't Postges bugs though since it's the most flexible type of backup to deal with application bugs with. The answer to whether you should roll back until 8.4.1 comes out will depend on how valuable your data is, how critical the downtime to repair any corruption would be, versus the time that you'll spend on rolling it back. That's a complicated calculus which will be different for every user. The bug found should only affect recovery though. So unless you have a standby slave database or have postgres or system crashes it shouldn't be relevant. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Install from Source On Windows - University of Sydney Research
Thanks Scott and Craig, I will give this a go today evening. Will let you know if it worked. Kushal On Mon, Aug 24, 2009 at 4:51 PM, Craig Ringer wrote: > On Mon, 2009-08-24 at 16:48 +1000, Kushal Vaghani wrote: > > okay guys I will get 8.2.13, so would running the install.pl would be > > different. > > > > I will try and see if I can compile and run as mentioned from the > > docs. But would there be any script after install.pl to run the > > postmaster, initDB > > You'll probably want to initdb, edit the postgresql.conf created by > initdb as desired, then start the postmaster manually using pg_ctl > (making sure to point pg_ctl at the data directory you created). It's > not hard. > > If you want to automate it, just write a batch file. Remember to use > ".cmd" not ".bat" so you run under the win32 cmd.exe script processor > not the ancient dos emulation monster command.com . > > -- > Craig Ringer > >
Re: [GENERAL] Install from Source On Windows - University of Sydney Research
okay guys I will get 8.2.13, so would running the install.pl would be different. I will try and see if I can compile and run as mentioned from the docs. But would there be any script after install.pl to run the postmaster, initDB Thanks. On Mon, Aug 24, 2009 at 4:40 PM, Scott Marlowe wrote: > On Mon, Aug 24, 2009 at 12:22 AM, Kushal Vaghani > wrote: > > Hey Craig > > > > I am doing a research project on a particular branch of postgreSQL and we > > have already had some code written on top of 8.2.4 base release few years > > back. I am doing some extensions to it. So thats the reason of not using > the > > latest releases. There would be lot of extra patching etc. > > No, there wouldn't. Going from 8.2.4 to 8.2.latest should be > painless, or nearly so. It's when the first two numbers change that > behaviour changes. Generally speaking a point release is just > security patches and bug fixes. On some very rare occasions there is > some behavioural change, but that's very rare and prominantly listed > in the release notes. >
[GENERAL] unsubscribe
unsubscribe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange "missing tables" problem
Denis BUCHER writes: > Yes that's correct. Therefore my "problem" is the "normal" behavior of > \dt. But "normal" means "expected". But I don't find it very > secure/handy, because you expect to see all your tables. Is there a way > to change the behavior of \dt so that it lists ALL tables present in > search path ? It *is* the expected behavior. The idea is that "\dt foo" should describe the same table that "select * from foo" would find. If you want to see all the possible matches for foo, use "\dt *.foo". regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting listed on "Community Guide to PostgreSQL GUI Tools"
Alvaro Herrera wrote on 24.08.2009 04:24: I think that page is pretty much unmaintained. Feel free to add your product, provided you don't turn it into a marketing opportunity (and be prepared for others to edit your description). Well, as it is a open source project, marketing doesn't really apply here :) As far as dead software, I think you should create a section at the end of the page and move dead projects there. How do I get an account to edit the page? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting listed on "Community Guide to PostgreSQL GUI Tools"
On 24/08/2009 16:47, Thomas Kellerer wrote: > How do I get an account to edit the page? You just sign up for a community account here: http://www.postgresql.org/community/signup Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange "missing tables" problem
Tom Lane a écrit : > Denis BUCHER writes: >> Yes that's correct. Therefore my "problem" is the "normal" behavior of >> \dt. But "normal" means "expected". But I don't find it very >> secure/handy, because you expect to see all your tables. Is there a way >> to change the behavior of \dt so that it lists ALL tables present in >> search path ? > > It *is* the expected behavior. The idea is that "\dt foo" should > describe the same table that "select * from foo" would find. > If you want to see all the possible matches for foo, use "\dt *.foo". > > regards, tom lane Oh yes, now I found the "list all tables" command : It's : \dt *.* Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] view table pkey values
Hello, Is it possible to select or otherwise view a table's primary key values? I'm troubleshooting the following error: ERROR: duplicate key value violates unique constraint "foo_pkey" The insert that yields the error seems innocuous enough: INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); It seems as if there's a sequence (foo_pkey) that's got some weird values in it. The table itself looks like this: CREATE TABLE foo ( foo_id SERIAL PRIMARY KEY, color_id INTEGER NOT NULL REFERENCES color(color_id) ON DELETE NO ACTION, ordinal INTEGER DEFAULT NULL, person_id INTEGER NOT NULL REFERENCES person(person_id) ON DELETE SET NULL ON UPDATE CASCADE, created timestamp DEFAULT CURRENT_TIMESTAMP); Thanks in advance, Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
On 24/08/2009 17:31, Scott Frankel wrote: > Is it possible to select or otherwise view a table's primary key values? [snip] > CREATE TABLE foo ( > foo_idSERIALPRIMARY KEY, select foo_id from foo; ? or am I missing something? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] warm standby and reciprocating failover
I wasn't sure which list is better suited, so this is cross posted from pgsql-admin. -Thanks On Fri, Aug 21, 2009 at 10:46 AM, james bardin wrote: > I have a working warm standby system, running 8.4 (thanks for urging > me to upgrade from the rehdat provided release). > One of the new requirements is going to be for (a non-DBA) admin to > easily swap services between the two servers for maintenance. > > The first move runs easily as expected- postgres ships the last > partial wal immediately on shutdown, trigger the standby and we're up. > I'm now running into issues bringing the first server back up in > standby mode. After the second server finishes recovery, the major > number of the wal files is incremented (say from 0001 to > 0002), and the 0002.history file is shipped back to the first > server. The first server however is still looking for 0001x files. > > Is there a way to ship back the missing information from the recovery > process, without doing another base backup of data/ ? On Mon, Aug 24, 2009 at 11:34 AM, james bardin wrote: > So I've been experimenting with this timeline problem without any success. > Is it possible that there are changes made during recovery that aren't logged? > > > I tried recovery_target_timeline='X' on the standby, where X is the > new timeline created after recovery on the new master. This fails, > with some "unexpected timeline ID" lines and a > PANIC: could not locate a valid checkpoint record > > I also tried using recovery_target_timeline='latest'. This fell back > gracefully to an earlier state, but changes were lost. Also, it never > waited on pg_standby, and finished recovering immediately. > > Although it doesn't solve this problem, can pg_standby be used with > recovery_target_timeline='latest', or should I file a bug? > > Thanks > -jim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
On 24/08/2009 17:31, Scott Frankel wrote: > The insert that yields the error seems innocuous enough: > > INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); > > It seems as if there's a sequence (foo_pkey) that's got some weird > values in it. The table itself looks like this: > > > CREATE TABLE foo ( > foo_idSERIALPRIMARY KEY, If the sequence's current value is lower than the highest foo_id in the table, then you'll get collisions - I'd imagine that's what's happening to you. You can fix that by using setval() to set the sequence value to a number higher than any currently in foo_id. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
Hi Ray, On Aug 24, 2009, at 9:48 AM, Raymond O'Donnell wrote: On 24/08/2009 17:31, Scott Frankel wrote: The insert that yields the error seems innocuous enough: INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); It seems as if there's a sequence (foo_pkey) that's got some weird values in it. The table itself looks like this: CREATE TABLE foo ( foo_idSERIALPRIMARY KEY, If the sequence's current value is lower than the highest foo_id in the table, then you'll get collisions If I understand how tables are managed internally, there are 2 sequences: my explicit foo_id and the internal sequence foo_foo_id_seq: public | foo_foo_id_seq | sequence | pguser | It's this internal sequence that must be involved in the collision, since I'm not specifying an insert value for my explicit foo_id column. You can fix that by using setval() to set the sequence value to a number higher than any currently in foo_id. Aha! So the explicit foo_id value cannot exceed the internal sequence, foo_foo_id_seq value? They should actually be the same, unless there've been insert errors, right? Is there a command that lists the values for the internal, foo_foo_id_seq, sequence? Thanks! Scott Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
Scott Frankel writes: > Is there a command that lists the values for the internal, > foo_foo_id_seq, sequence? select * from foo_foo_id_seq; The usual way to get into this sort of trouble is to load a bunch of data into the table while explicitly specifying ID values. It will take the data (as long as it doesn't conflict with existing IDs) but nothing happens to the sequence. pg_dump knows it has to update the sequence too, but a lot of other tools don't; and even with pg_dump a selective restore can mess things up. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
Got it! Yes, this started happening after loading from a pg_dump. Thanks for the explanation! Scott On Aug 24, 2009, at 10:52 AM, Tom Lane wrote: Scott Frankel writes: Is there a command that lists the values for the internal, foo_foo_id_seq, sequence? select * from foo_foo_id_seq; The usual way to get into this sort of trouble is to load a bunch of data into the table while explicitly specifying ID values. It will take the data (as long as it doesn't conflict with existing IDs) but nothing happens to the sequence. pg_dump knows it has to update the sequence too, but a lot of other tools don't; and even with pg_dump a selective restore can mess things up. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] view table pkey values
On 24/08/2009 18:37, Scott Frankel wrote:
> If I understand how tables are managed internally, there are 2
> sequences: my explicit foo_id and the internal sequence
> foo_foo_id_seq:
>
> public | foo_foo_id_seq | sequence | pguser |
>
> It's this internal sequence that must be involved in the collision,
> since I'm not specifying an insert value for my explicit foo_id
> column.
Your column foo_id is just that - a column . It's not a sequence. It's
an integer column which is specified to take it's default value from a
sequence, which Postgres creates for you and names foo_foo_id_seq.
In fact, "serial" isn't a real type - its syntactic sugar that -
(i) creates the sequence, named __seq,
(ii) creates the column as type integer,
(iii) makes the sequence to be owned by the column, and
(iv) sets the default value of the column as nextval().
The "serial" pseudo-type just saves you doing all this by hand.
When you don't enter an explicit value for the "Serial" column, the
specified default value gets entered instead, which is the return value
of the function nextval('foo_foo_id_seq'). You can of course enter an
explicit value into the column, and then the default is ignored; by the
same token, the associated sequence doesn't get incremented, so this can
lead to collisions if you're not careful.
For example:
postgres=# create table test(a serial primary key, b text);
NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for
serial column "test.a"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
postgres=# insert into test(b) values('This will work');
INSERT 0 1
postgres=# select * from test;
a | b
---+
1 | This will work
(1 row)
postgres=# select currval('test_a_seq');
currval
-
1
(1 row)
postgres=# insert into test(a, b) values(2, 'This works too');
INSERT 0 1
postgres=# select * from test;
a | b
---+
1 | This will work
2 | This works too
(2 rows)
postgres=# select currval('test_a_seq');
currval
-
1
(1 row)
postgres=# insert into test(b) values('This will bomb');
ERROR: duplicate key value violates unique constraint "test_pkey"
postgres=# select currval('test_a_seq');
currval
-
2
(1 row)
You can read all about it here:
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL
I hope all this helps. :-)
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
On Mon, Aug 24, 2009 at 12:10:30AM -0600, Scott Marlowe wrote: > On Sat, Aug 22, 2009 at 4:55 PM, Greg Sabino Mullane wrote: > > A server crash is a pretty rare event in the Postgres world, so I > > would not spend too many cycles on this... > > I've been running pg in production since 7.0 came out. zero server > crashes. In my experience, OS crashes are much more common than PostgreSQL crashes. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
On Mon, Aug 24, 2009 at 12:41 PM, David Fetter wrote: > On Mon, Aug 24, 2009 at 12:10:30AM -0600, Scott Marlowe wrote: >> On Sat, Aug 22, 2009 at 4:55 PM, Greg Sabino Mullane >> wrote: >> > A server crash is a pretty rare event in the Postgres world, so I >> > would not spend too many cycles on this... >> >> I've been running pg in production since 7.0 came out. zero server >> crashes. > > In my experience, OS crashes are much more common than PostgreSQL > crashes. Also, admin mistakes are more common than pgsql crashes. I've done things like type "sudo reboot" into my workstation only realize seconds later that I'm logged into a production server (long time ago, but still). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: > On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: > > I can't really think of any real reason to put the field at a > > particular position, applications don't reallty care about the order > > of fields. > > Because it's very convenient for ad-hoc queries! PG currently assumes > that the column order is the same as when it was created but there are > (unimplemented) suggestions about how to "fix" this. See for example: > > http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php > > -- > Sam http://samason.me.uk/ > But how is it convenient exactly, is it just a timesaver so you can SELECT * instead of having to type SELECT firstname, lastname, email? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
From: vinny Subject: Re: R: [GENERAL] Field's position in Table To: "Sam Mason" Cc: pgsql-general@postgresql.org Date: Monday, August 24, 2009, 2:38 PM On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: > On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: > > I can't really think of any real reason to put the field at a > > particular position, applications don't reallty care about the order > > of fields. > > Because it's very convenient for ad-hoc queries! PG currently assumes > that the column order is the same as when it was created but there are > (unimplemented) suggestions about how to "fix" this. See for example: > > http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php > > -- > Sam http://samason.me.uk/ > But how is it convenient exactly, is it just a timesaver so you can SELECT * instead of having to type SELECT firstname, lastname, email? For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file. Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public. Most programmers habitually organize their source code to keep related functions together. It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions. And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones. Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code. So when I'm working in PgAdmin, I like to see my primary & foreign keys listed first, then data fields in logical groupings, and finally the standard "footer" fields we add to all tables like create & update by/date. Whenever I'm developing and need to reference a table definition, (or do a select * in pgAdmin for sample data) I lose productivity having to scan through all the fields repeatedly instead of seeing at a glance the fields I want because I know where they *should* be in the listing. Sometimes I have to scan through the fields several times before I finally see the one I want, because it was in the middle of unrelated items. I *never* code my applications to depend on field order; I'm referring to development convenience only. (Just my two cents, YMMV, etc)
Re: R: [GENERAL] Field's position in Table
Adam Rich wrote: > For me, saying all new fields must go at the end of the table is like > saying all new functions must go at the end of your C source file. > Not that it makes *any* difference to the end user, or other > applications using your libraries, but as developers we tend to > be more organized than the general public. Just because we don't have it implemented does not make it a bad idea. I think (and others do as well) it's a good idea to be able to handle this; it's just that nobody has gotten around to implement it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
Hello! > If, however, you mean a crash of the server machine PostgreSQL is > runnning on, which is MUCH more likely and will have different > effects/behaviour, then Ray Stell's advice to bring the interface down > is probably pretty good. Sorry for a bit ambiguous usage of both "crash" and "fault" terms. By those words I meant crash of the server machine PostgreSQL is running on, not the PostgreSQL itself. Network outages between client and PostgreSQL are also kind of something I would like to simulate in any way. Though I don't think there are any differences between the crash of PosgreSQL itself and the crash of the machine PostgreSQL is running on from the client's point of view. Yet another way to simulate this terrible behaviour I've found is to stop PostgreSQL by "pg_ctl -m immediate" command. Thanks to all who has answered in this topic! It was very helpful to read it! -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
Adam Rich wrote: For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file. Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public. Most programmers habitually organize their source code to keep related functions together. It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions. And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones. Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code. otoh, reordering the fields in a table would likely require a global exclusive access lock on the table for the duration of the operation, which for a large table could be substantial. AFAIK, the current ALTER TABLE ... ADD COLUMN just locks the table for updates, the pre-existing fields can still be SELECTed until the ALTER completes and the new columns become visible. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What approach should I use instead of creating tables on the fly?
Hello, Craig. > Table partitioning and table inheritance. See the manual and the list > archives. Thanks for mentioning "partitioning" feature! It seems to be what I've been looking for so far. -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
Hello! > You should also test your client's response to the Pg server remaining > up but becoming non-responsive (eg: failed disk array causes Pg backends > to remain in uninterruptable disk I/O system calls in the kernel). A > possibly good way to do this is to SIGSTOP the backend(s). I haven't thought about it yet. It's possible the place where I should use timeouts on the operations involving calls to PostgreSQL. -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
John R Pierce wrote: > otoh, reordering the fields in a table would likely require a global > exclusive access lock on the table for the duration of the > operation, which for a large table could be substantial. Obviously you haven't read the previous proposal on how to handle it. It doesn't require rewriting the whole table. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
- Original Message - From: Adam Rich To: Sam Mason ; vinny Cc: pgsql-general@postgresql.org Sent: Monday, August 24, 2009 2:58 PM Subject: Re: R: [GENERAL] Field's position in Table From: vinny Subject: Re: R: [GENERAL] Field's position in Table To: "Sam Mason" Cc: pgsql-general@postgresql.org Date: Monday, August 24, 2009, 2:38 PM On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: > On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: > > I can't really think of any real reason to put the field at a > > particular position, applications don't reallty care about the order > > of fields. > > Because it's very convenient for ad-hoc queries! PG currently assumes > that the column order is the same as when it was created but there are > (unimplemented) suggestions about how to "fix" this. See for example: > > http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php > > -- > Sam http://samason.me.uk/ > But how is it convenient exactly, is it just a timesaver so you can SELECT * instead of having to type SELECT firstname, lastname, email? For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file. Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public. Most programmers habitually organize their source code to keep related functions together. It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions. And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones. Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code. So when I'm working in PgAdmin, I like to see my primary & foreign keys listed first, then data fields in logical groupings, and finally the standard "footer" fields we add to all tables like create & update by/date. Whenever I'm developing and need to reference a table definition, (or do a select * in pgAdmin for sample data) I lose productivity having to scan through all the fields repeatedly instead of seeing at a glance the fields I want because I know where they *should* be in the listing. Sometimes I have to scan through the fields several times before I finally see the one I want, because it was in the middle of unrelated items. I *never* code my applications to depend on field order; I'm referring to development convenience only. (Just my two cents, YMMV, etc) Just another two cents agreeing here. I think programmers tend to be a bit anal about this sort of thing. True, it makes no material difference but one just tends to be more comfortable with everything nicely organized. Bayless
Re: [GENERAL] How to simulate crashes of PostgreSQL?
On Tue, 2009-08-25 at 00:26 +0400, Sergey Samokhin wrote: > Hello! > > > If, however, you mean a crash of the server machine PostgreSQL is > > runnning on, which is MUCH more likely and will have different > > effects/behaviour, then Ray Stell's advice to bring the interface down > > is probably pretty good. > > Sorry for a bit ambiguous usage of both "crash" and "fault" terms. By > those words I meant crash of the server machine PostgreSQL is running > on, not the PostgreSQL itself. Network outages between client and > PostgreSQL are also kind of something I would like to simulate in any > way. This is the reference I should've given: http://www.linuxfoundation.org/en/Net:Netem -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
On Tue, 2009-08-25 at 00:26 +0400, Sergey Samokhin wrote: > Hello! > > > If, however, you mean a crash of the server machine PostgreSQL is > > runnning on, which is MUCH more likely and will have different > > effects/behaviour, then Ray Stell's advice to bring the interface down > > is probably pretty good. > > Sorry for a bit ambiguous usage of both "crash" and "fault" terms. By > those words I meant crash of the server machine PostgreSQL is running > on, not the PostgreSQL itself. Network outages between client and > PostgreSQL are also kind of something I would like to simulate in any > way. Get a cheap PC with two Ethernet cards running Linux, and put it between your Pg server and the rest of the network - or between your client and the rest of the network. Set it up to route packets between the two interfaces using iptables. You can now easily introduce rules to do things like drop random packets, drop packets of particular sizes, drop a regular percentage of packets, etc. You can also introduce latency using iproute2's `tc' . http://lartc.org/ example: http://www.kdedevelopers.org/node/1878 showing the use of the "delay" option of the network emulation (netem) qdisc. Alternately: brtables lets you do some network issue simulation on a Linux machine that's bridging between two interfaces instead of routing between them, so you can make your router transparent to the network. Unless you've worked a bit with iptables before or at least done a lot of general networking work you'll need to do a bit of learning to get much of this up and running smoothly. It's not a trivial drop-in. I'm not going to give detailed instructions and support, as I just don't have the time to go into it at present - sorry. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to simulate crashes of PostgreSQL?
On Tue, 2009-08-25 at 00:26 +0400, Sergey Samokhin wrote: > Though I don't think there are any differences between the crash of > PosgreSQL itself and the crash of the machine PostgreSQL is running on > from the client's point of view. There certainly are! For one thing, if a client with an established connection sends a packet to a machine where PostgreSQL has crashed (the backend process has exited on a signal) it'll receive a TCP RST indicating that the connection has been broken. The OS will also generally FIN to the client when the backend crashes to inform it that the connection is closing, so you'll often find out as soon as the backend dies or at least as soon as you next try to use the connection. If the issue was just with that backend, your client can just reconnect, retry its most recent work, and keep on going. Similarly, a new client trying to connect to a machine where the postmaster has crashed will receive a TCP RST packet indicating that the connection attempt was actively refused. It'll know immediately that something's not right and will get a useful error from the TCP stack. If, on the other hand, the server has crashed, clients may not receive any response at all to packets. The server may even stop responding to ARP requests, in which case the nearest router to it will - eventually, maybe - send your client an ICMP destination-unreachable . There will be long delays either way before the TCP/IP stack decides the connection has died. Your client will probably block on recv(...) / read(...) for an extended period. If a backend is still running but in a nonresponsive state, the TCP/IP stack on the server will still ACK packets you send to the backend (at least until the buffers fill up), but the backend won't be doing anything with the data. The local TCP stack won't see anything wrong because, at the TCP level, there isn't - something that can't happen in a server crash. So, yes, there's a pretty big difference between a crash of PostgreSQL and a server crash. Behaviour is different from the client perspective and you need to consider that. Intermediate network issues are different again, as you might encounter huge latency (possibly randomly only on some packets), random packet loss, etc. This will cause weird pauses and delays in communication that your client must cope with. This, by the way, is one of the reasons you *really* should do all your database work in a separate worker thread on GUI clients. The GUI must remain responsive even when you're waiting for a response that'll never come, or being held up by multi-second network latencies. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [NOVICE] Re: [GENERAL] Install from Source On Windows - University of Sydney Research
Just to clarify a minor point - on Windows NT and newer, the COMSPEC environment variable determines which executable runs a batch script whether the extension is .bat or .cmd - on NT and newer this defaults to cmd.exe. -Original Message- From: pgsql-novice-ow...@postgresql.org [mailto:pgsql-novice-ow...@postgresql.org] On Behalf Of Craig Ringer Sent: Sunday, August 23, 2009 11:51 PM To: Kushal Vaghani Cc: Scott Marlowe; pgsql-general@postgresql.org; pgsql-nov...@postgresql.org Subject: [NOVICE] Re: [GENERAL] Install from Source On Windows - University of Sydney Research On Mon, 2009-08-24 at 16:48 +1000, Kushal Vaghani wrote: > okay guys I will get 8.2.13, so would running the install.pl would be > different. > > I will try and see if I can compile and run as mentioned from the > docs. But would there be any script after install.pl to run the > postmaster, initDB You'll probably want to initdb, edit the postgresql.conf created by initdb as desired, then start the postmaster manually using pg_ctl (making sure to point pg_ctl at the data directory you created). It's not hard. If you want to automate it, just write a batch file. Remember to use ".cmd" not ".bat" so you run under the win32 cmd.exe script processor not the ancient dos emulation monster command.com . -- Craig Ringer -- Sent via pgsql-novice mailing list (pgsql-nov...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
