[GENERAL] noobie join question
Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. create table types ( id integer, descr varchar(30) ); COPY types (id, descr) FROM stdin; 1descr 1 2descr 2 3descr 3 4descr 4 \. create table units ( uid integer, udevice varchar(30), utype integer ); COPY units (uid, udevice, utype) FROM stdin; 1a1 2b1 3c4 4d3 \. create table assoc ( aid integer, src_id integer, dest_id integer ); COPY assoc (aid, src_id, dest_id) FROM stdin; 112 213 334 442 \. desired result a | descr 1 | b | descr 1 a | descr 1 | c | descr 4 c | descr 4 | d | descr 3 d | descr 3 | b | descr 1 Thanks, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restarting DB after moving to another drive
I am working on windows and I had to move my database on another hard drive after the original one started overheating. In order to move the DB I did the following. -Stop postgresql-x64-9.3 service - and wait until there were no more system access to on the original drive -Copy the entire content of the original drive to the new one (the drive is dedicated to the DB) -Turn off the original hard drive and reassign the old drive letter to the new one -Restart the DB I tried to connect to the database by using PgAdmin III and I got the following error message: Could not read symbolic link pg_tblspc/100589: Invalid argument I concluded something went wrong and I decided to get back to the old drive. I stopped the DB, turned off the new drive, turned on the old one and tried to restart the postgresql service but it does not start anymore. The only message I get is: Postgresql-x64-9.3 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs I am the only user of the database Hoping someone can help Daniel
Re: [GENERAL] noobie join question
On Mon, 2015-05-11 at 06:46 -0400, Steve Clark wrote: Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. SELECT us.udevice, ts.descr, ud.udevice, td.descr FROM assoc AS a LEFT JOIN units AS us ON a.src_id = us.uid LEFT JOIN types AS ts ON us.utype = ts.id LEFT JOIN units AS ud ON a.dest_id = ud.uid LEFT JOIN types AS td ON ud.utype = td.id; -- 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] Restarting DB after moving to another drive
On 11/05/2015 12:03, Daniel Begin wrote: I am working on windows and I had to move my database on another hard drive after the original one started overheating. In order to move the DB I did the following… -Stop postgresql-x64-9.3 service – and wait until there were no more system access to on the original drive -Copy the entire content of the original drive to the new one (the drive is dedicated to the DB) -Turn off the original hard drive and reassign the old drive letter to the new one -Restart the DB I tried to connect to the database by using PgAdmin III and I got the following error message: “Could not read symbolic link “pg_tblspc/100589”: Invalid argument” I concluded something went wrong and I decided to get back to the old drive. I stopped the DB, turned off the new drive, turned on the old one and tried to restart the postgresql service but it does not start anymore. The only message I get is: “Postgresql-x64-9.3 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs” Hi there, Sounds like you're on Windows - you can get more information from PostgreSQL's own logs, which by default on Windows are in a directory called pg_log under the data directory. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Comparing txid and xmin (under BDR)
Hi, I would really like to be able to externally to Postgres at some point in time later, be able to compare the txid of 2 queries. Namely: The INSERT transaction for a certain row in a table, and The SELECT transaction reading some other data. With the one caveat that this has to work with BDR. So ... I can easily get the current txid of the SELECT transaction by calling txid_current(). However, - I can't in general compare it to the xmin of the table row. BDR does ensure (it seems) that xmin is meaningfull within the local node xid context. But after a xid wrap-around all txids will be larger than any xmin. And I can't get the epoch of the xmin value. BUT ... will this work: ? Comparing txid_current() of the SELECT transaction, to txid-current()-age(xmin) of the table row? /Peter -- 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] noobie join question
Steve Clark wrote: I am having trouble trying to figure out how to get the result listed at the bottom. That's a bit tough, since you don't describe the desired result. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. create table types ( id integer, descr varchar(30) ); COPY types (id, descr) FROM stdin; 1descr 1 2descr 2 3descr 3 4descr 4 \. create table units ( uid integer, udevice varchar(30), utype integer ); COPY units (uid, udevice, utype) FROM stdin; 1a1 2b1 3c4 4d3 \. create table assoc ( aid integer, src_id integer, dest_id integer ); COPY assoc (aid, src_id, dest_id) FROM stdin; 112 213 334 442 \. These tables should have foreign key constraints to each other, so that we can understand how they are related and to make sure that no impossible values are inserted. desired result a | descr 1 | b | descr 1 a | descr 1 | c | descr 4 c | descr 4 | d | descr 3 d | descr 3 | b | descr 1 If my guesses are correct, the query would be SELECT u1.udevice, t1.descr, u1.udevice, t1.descr FROM assoc a JOIN units u1 ON (a.src_id = u1.uid) JOIN types t1 ON (u1.utype = t1.id) JOIN units u2 ON (a.dest_id = u2.uid) JOIN types t2 ON (u2.utype = t2.id); I did not test this. Yours, Laurenz Albe -- 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] Restarting DB after moving to another drive
I just get it back running with the old drive - was some Windows hidden behavior! However, does someone could tell me what went wrong with the procedure I used to move the DB? And/or what procedure I should have used in order to get it right? Daniel -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: May-11-15 07:50 To: Daniel Begin; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive On 11/05/2015 12:03, Daniel Begin wrote: I am working on windows and I had to move my database on another hard drive after the original one started overheating. In order to move the DB I did the following. -Stop postgresql-x64-9.3 service - and wait until there were no more system access to on the original drive -Copy the entire content of the original drive to the new one (the drive is dedicated to the DB) -Turn off the original hard drive and reassign the old drive letter to the new one -Restart the DB I tried to connect to the database by using PgAdmin III and I got the following error message: Could not read symbolic link pg_tblspc/100589: Invalid argument I concluded something went wrong and I decided to get back to the old drive. I stopped the DB, turned off the new drive, turned on the old one and tried to restart the postgresql service but it does not start anymore. The only message I get is: Postgresql-x64-9.3 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs Hi there, Sounds like you're on Windows - you can get more information from PostgreSQL's own logs, which by default on Windows are in a directory called pg_log under the data directory. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] noobie join question
On 05/11/2015 07:16 AM, Oliver Elphick wrote: On Mon, 2015-05-11 at 06:46 -0400, Steve Clark wrote: Hi List, I am having trouble trying to figure out how to get the result listed at the bottom. I have 3 tables units, types of units which has a description of the units, and a table that list associations of the units. I can't figure out how to do the proper joins. Any pointers would be appreciated. SELECT us.udevice, ts.descr, ud.udevice, td.descr FROM assoc AS a LEFT JOIN units AS us ON a.src_id = us.uid LEFT JOIN types AS ts ON us.utype = ts.id LEFT JOIN units AS ud ON a.dest_id = ud.uid LEFT JOIN types AS td ON ud.utype = td.id; Thanks Oliver - that worked perfectly. -- Stephen Clark *NetWolves Managed Services, LLC.* Director of Technology Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] Restarting DB after moving to another drive
On 11/05/2015 13:38, Daniel Begin wrote: I just get it back running with the old drive - was some Windows hidden behavior! However, does someone could tell me what went wrong with the procedure I used to move the DB? And/or what procedure I should have used in order to get it right? I've never done it myself, but I understand that one way to do it is with tablespaces: http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html As I understand it, you create a new tablespace on the new disk, then move your database objects to it. How that plays with Windows, I've no idea I'd guess that changing the drive letter might well cause Bad Things to happen. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 clean/truncate / VACUUM FULL pg_largeobject without (much) downtime?
Hi Muthusamy: On Mon, May 11, 2015 at 11:48 AM, Muthusamy, Sivaraman sivaraman.muthus...@in.verizon.com wrote: With regards to this pg_largeobject, I have the following questions: - What is this pg_largetobject ? Just seeking it in the alpha index leads you here: http://www.postgresql.org/docs/9.4/static/catalog-pg-largeobject.html - what does it contain ? tried PostgreSQL documentation and lists, but could not get much from it. The large object data. Read the docs again, there is a chapter devoted to it: http://www.postgresql.org/docs/9.4/static/largeobjects.html where it tells you ( under implementation features, http://www.postgresql.org/docs/9.4/static/lo-implementation.html ) why you have serveral 'duplicate rows ( loid ) ( it is because large objects are split in chunks ) - why does it grow ? Because you modify or writ to it. - Was there any configuration change that may have triggered this to grow? For last one year or so, there was no problem, but it started growing all of sudden in last two weeks. The only change we had in last two weeks was that we have scheduled night base-backup for it and auto-vacuum feature enabled. You should have a look, specially if you modify it, as maybe it is not getting vacuumed correctly. I do not recall the relevant commands, but they are all in the manual. - pg_largeobject contains so many duplicate rows (loid). Though there are only about 0.6 million rows (LOIDs), but the total number of rows including duplicates are about 59million records. What are all these ? As I commented above, LO are split in chunks, of 2K according to the docs in one of the above links. Your numbers hint at .6M objects of about 200k, 100 chunks each, for about 60M records. At 1 chunk page, with very bad luck, this could easily go to 480G, 120G if you've got them 4 to a page, so 200G does not seem that big ( I do not know if TOAST compression is in play here, but the numbers look ok to me if the data is not append-only. Francisco Olarte. -- 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] Restarting DB after moving to another drive
Thank for the link! Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? Regards, Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell Sent: May-11-15 09:19 To: Daniel Begin; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive On 11/05/2015 13:38, Daniel Begin wrote: I just get it back running with the old drive - was some Windows hidden behavior! However, does someone could tell me what went wrong with the procedure I used to move the DB? And/or what procedure I should have used in order to get it right? I've never done it myself, but I understand that one way to do it is with tablespaces: http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html As I understand it, you create a new tablespace on the new disk, then move your database objects to it. How that plays with Windows, I've no idea I'd guess that changing the drive letter might well cause Bad Things to happen. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Restarting DB after moving to another drive
( OOps, forgot to cc the list again in previous, quoting all message for context ). On Mon, May 11, 2015 at 3:14 PM, Daniel Begin jfd...@hotmail.com wrote: Makes sense considering the error message! Maybe an image backup would make the job... I do not know how they are, made in windows, but it should. Also, IIRC there were versions of the GNU unix common utilities ( cp, rm, mv, ln ) for windows which correctly worked with links, if you can get hold of a reaosnable windows tar a 'tar c | tar x' should do the trick too. Also, look at the copy command help, in linux I know cp -a ( for archive ) does normally the trick. Francisco Olarte. -Original Message- From: Francisco Olarte [mailto:fola...@peoplecall.com] Sent: May-11-15 08:55 To: Daniel Begin Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel. On Mon, May 11, 2015 at 2:38 PM, Daniel Begin jfd...@hotmail.com wrote: I just get it back running with the old drive - was some Windows hidden behavior! However, does someone could tell me what went wrong with the procedure I used to move the DB? It's been more than a decade since I've used windows, but from my past experience I'll bet you did not copy the DB right ( you did not copy the symbolic links, IIRC windows normal tools did not cope with them too well, but that was on w2k, YMMV ). Francisco Olarte. -- 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 clean/truncate / VACUUM FULL pg_largeobject without (much) downtime?
Hi Group, Facing a problem where pg_catalog.pg_largetobject has been growing fast recently, in last two weeks. The actual data itself, in user tables, is about 60GB, but pg_catalog.pg_largeobject table is 200GB plues. Please let me know how to clean/truncate this table without losing any user data in other table. With regards to this pg_largeobject, I have the following questions: - What is this pg_largetobject ? - what does it contain ? tried PostgreSQL documentation and lists, but could not get much from it. - why does it grow ? - Was there any configuration change that may have triggered this to grow? For last one year or so, there was no problem, but it started growing all of sudden in last two weeks. The only change we had in last two weeks was that we have scheduled night base-backup for it and auto-vacuum feature enabled. - pg_largeobject contains so many duplicate rows (loid). Though there are only about 0.6 million rows (LOIDs), but the total number of rows including duplicates are about 59million records. What are all these ? Kindly help getting this information and getting this issue cleared, and appreciate your quick help on this. Thanks and Regards M.Shiva
Re: [GENERAL] Restarting DB after moving to another drive
Hi Daniel. On Mon, May 11, 2015 at 4:42 PM, Daniel Begin jfd...@hotmail.com wrote: Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. Francisco Olarte. -- 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] Restarting DB after moving to another drive
Francisco wrote: How big/critical is your database? How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) Francisco wrote: just did a stop/cp/change pgdata /restart, I suppose windows must have comparable ways This is what I have just tried when I got “Could not read symbolic link “pg_tblspc/100589”: Invalid argument” Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 To: Daniel Begin Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel. On Mon, May 11, 2015 at 4:42 PM, Daniel Begin jfd...@hotmail.com wrote: Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. Francisco Olarte. -- 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] Why does this SQL work?
2015-05-11 19:26 GMT+03:00 Anil Menon gakme...@gmail.com: manualscan= select count(*) From public.msgtxt where msgid in (select msgid From ver736.courier where org_id=3); count --- 10225 (1 row) Please note, there is no msgid col in courier table. Which brings the question why does this SQL work? An select msgid From courier where org_id=3 by itself gives error column msgid does not exist. Because you can reference both, inner and outer columns from the inner query. Here you're most likely referring to the outer `msgid` in the subquery. That's why it is always a good idea to prefix all your columns with tables aliases. -- Victor Y. Yegorov
Re: [GENERAL] Restarting DB after moving to another drive
Interesting, The symbolic links on the old drive -still used by the DB- look like windows' shortcuts to parent folder, while they are empty folders in the copy of the database I have on the new drive... When I do a plane copy of those links on another drive I also get the same empty folders. I am getting closer to the initial problem, and closer to the solution! Maybe an image backup of the drive would have done the job properly instead of using a plane copy? Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Marc Mamin Sent: May-11-15 13:10 To: Daniel Begin; 'Francisco Olarte' Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi, have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid ? They possibly still point to the old drive. I guess you have to correct them per hand before starting the moved DB. regards, Marc Mamin Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]quot; im Auftrag von quot;Daniel Begin [jfd...@hotmail.com] Gesendet: Montag, 11. Mai 2015 17:30 An: 'Francisco Olarte' Cc: r...@iol.ie; pgsql-general@postgresql.org Betreff: Re: [GENERAL] Restarting DB after moving to another drive Francisco wrote: How big/critical is your database? How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) Francisco wrote: just did a stop/cp/change pgdata /restart, I suppose windows must have comparable ways This is what I have just tried when I got Could not read symbolic link pg_tblspc/100589: Invalid argument Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 To: Daniel Begin Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel. On Mon, May 11, 2015 at 4:42 PM, Daniel Begin jfd...@hotmail.com wrote: Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. Francisco Olarte. -- 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 -- 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] Why does this SQL work?
On Tue, May 12, 2015 at 12:26:15AM +0800, Anil Menon wrote: manualscan= select count(*) From msgtxt where msgid in ( manualscan( select msgid From courier where org_id=3 manualscan( ) manualscan- ; count --- 10225 (1 row) manualscan= select count(*) From public.msgtxt where msgid in (select msgid From ver736.courier where org_id=3); count --- 10225 (1 row) Please note, there is no msgid col in courier table. Which brings the question why does this SQL work? An select msgid From courier where org_id=3 by itself gives error column msgid does not exist. This works because this is correlated subquery. You should have always use aliases to avoid such errors. Like here: select count(*) From msgtxt as m where m.msgid in ( select c.msgid from courier c where c.org_id = 3 ); Your query is equivalent to: select count(*) From msgtxt as m where m.msgid in ( select m.msgid from courier c where c.org_id = 3 ); which returns all rows from msgtxt if there is at least one row in courier with org_id = 3. depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does this SQL work?
Hi,
I have the following setup :
manualscan= set search_path=ver736,public;
SET
manualscan= \d courier;
Table ver736.courier
Column | Type |Modifiers
---++--
org_id| smallint | not null default
nextval('courier_org_id_seq'::regclass)
courier_name | character varying(500) | not null
courier_code | character varying(50) |
is_valid | boolean|
universe_id | character varying(50) |
courier_image | bytea |
Indexes:
courier_pk PRIMARY KEY, btree (org_id)
courier_code_un UNIQUE CONSTRAINT, btree (courier_code)
courier_name_un UNIQUE CONSTRAINT, btree (courier_name)
courier_code_idx btree (courier_code)
Referenced by:
xxx...xxx...(a few tables)
manualscan= \d msgtxt;
Table public.msgtxt
Column | Type |
Modifiers
---+--+
msgid | integer | not null default
nextval('msgtxt_msgid_seq'::regclass)
msgval| text |
transaction_stamp | timestamp with time zone | default now()
corelationid | text |
deviverymode | integer |
destination | text |
expiration| integer |
messageid | text |
priority | integer |
redelivered | boolean |
replyto | text |
timestamp | bigint |
msgtype | text |
senderid | text |
Indexes:
msgtxt_pkey PRIMARY KEY, btree (msgid)
Triggers:
manual_scan_tx_tr AFTER INSERT ON msgtxt FOR EACH ROW EXECUTE PROCEDURE
process_manual_scan_tx()
manualscan= select count(*) From msgtxt where msgid in (
manualscan( select msgid From courier where org_id=3
manualscan( )
manualscan- ;
count
---
10225
(1 row)
manualscan= select count(*) From public.msgtxt where msgid in (select
msgid From ver736.courier where org_id=3);
count
---
10225
(1 row)
Please note, there is no msgid col in courier table. Which brings the
question why does this SQL work? An select msgid From courier where
org_id=3 by itself gives error column msgid does not exist.
OS Version : Centos 7
PG Version : PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
Regards
AK
Re: [GENERAL] RPM building tools and info missing?
On 5/9/15 10:47 AM, Bill Moran wrote: https://wiki.postgresql.org/wiki/RPM_Packaging The link to the specfiles and other data at http://svn.pgrpms.org/repo/ gives a 404. It's been move to git. I have updated the wiki page with the new URL. -- 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] Comparing txid and xmin (under BDR)
On 11 May 2015 at 21:10, Peter Mogensen a...@one.com wrote: Hi, I would really like to be able to externally to Postgres at some point in time later, be able to compare the txid of 2 queries. Namely: The INSERT transaction for a certain row in a table, and The SELECT transaction reading some other data. With the one caveat that this has to work with BDR. So ... I can easily get the current txid of the SELECT transaction by calling txid_current(). Note that by doing so, you force txid allocation for a read-only query that might otherwise not need one, which increases your txid burn rate and decreases time until you need to do wraparound-protection vacuuming. However, - I can't in general compare it to the xmin of the table row. BDR does ensure (it seems) that xmin is meaningfull within the local node xid context. Or, rather, it doesn't do anything different to what PostgreSQL its self does. I'm still not at all convinced that your desire to use transaction IDs for the cache invalidation stuff you're doing makes sense or will work sensibly even in a single-node environment. It's certainly not going to when comparing between nodes, especially in an async system. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] Comparing txid and xmin (under BDR)
On 2015-05-12 06:06, Craig Ringer wrote: On 11 May 2015 at 21:10, Peter Mogensen a...@one.com wrote: So ... I can easily get the current txid of the SELECT transaction by calling txid_current(). Note that by doing so, you force txid allocation for a read-only query that might otherwise not need one, which increases your txid burn rate and decreases time until you need to do wraparound-protection vacuuming. Good point. Or, rather, it doesn't do anything different to what PostgreSQL its self does. I'm still not at all convinced that your desire to use transaction IDs for the cache invalidation stuff you're doing makes sense or will work sensibly even in a single-node environment. It's certainly not going to when comparing between nodes, especially in an async system. You could be right. Let's recap So - for an external cache in a system with high read-to-write ratio we can get very high cache hit ratios by having no TTL, but doing cache invalidation. (about 98% i practice, so xid burn rate is probably not as bad as is may sound) Invalidation events have 1 problem. (apart from how they are generated). There's a race condition. We need to ensure that this sequence of events does not happen: 1) cache miss 2) DB read 3) DB invalidation event 4) cache invalidation applied 5) caching of the value read in 2) In a standard PostgreSQl setup, we can stop 5) from happening, by attaching txid_current() to the invalidation event in 3) and txid_snapshot_xmin() to the cache read and put a tombstone in the cache when doing invalidations. (with a relative long TTL) So ... when the value in 5) is about to get cached and if hits a tombstone in the cache it is only cached if the tombstone txid is older than the txid_snapshot_xmin of the new read - ie. if were sure that the invalidation took place before the value we're about to cache. This scheme should work with standard Postgres. Also read-only slaves. But it won't work with BDR, since each node got its' own txid namespace. Attaching txid_current() to an invalidation event has no meaning on other nodes. On the other hand. ... it the invalidation event is stored in a BDR replicated table, then the xmin of the event row will have a meaning to the local node. Which was the only way I found to get the invalidation event placed in the local sequence of transactions. /Peter -- 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] Restarting DB after moving to another drive
Hi, have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid ? They possibly still point to the old drive. I guess you have to correct them per hand before starting the moved DB. regards, Marc Mamin Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]quot; im Auftrag von quot;Daniel Begin [jfd...@hotmail.com] Gesendet: Montag, 11. Mai 2015 17:30 An: 'Francisco Olarte' Cc: r...@iol.ie; pgsql-general@postgresql.org Betreff: Re: [GENERAL] Restarting DB after moving to another drive Francisco wrote: How big/critical is your database? How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) Francisco wrote: just did a stop/cp/change pgdata /restart, I suppose windows must have comparable ways This is what I have just tried when I got “Could not read symbolic link “pg_tblspc/100589”: Invalid argument” Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 To: Daniel Begin Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel. On Mon, May 11, 2015 at 4:42 PM, Daniel Begin jfd...@hotmail.com wrote: Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. Francisco Olarte. -- 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
