Re: [GENERAL] pg_dump -s dumps data?!
hubert depesz lubaczewski writes: > I have weird situation. > pg 9.1.2, compilet by our own script from source, on 10+ machines. > on fours machines, pg_dump -s - dumps with data!: > postgres@machine:~$ pg_dump --verbose --schema-only dbname > q > ... > pg_dump: creating TABLE x1 > pg_dump: restoring data for table "x2" > pg_dump: dumping contents of table x2 > pg_dump: restoring data for table "x3" > pg_dump: dumping contents of table x3 > ... > What could be wrong? Do the command lines actually look exactly like that? Some platforms are forgiving about violation of the switch-then-argument order (ie, putting switches after the database name) and some are not. I seem to recall that Solaris is particularly strange about this, so what platform(s) are we talking about anyway? 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] pg_dump -s dumps data?!
On 01/27/2012 03:05 PM, hubert depesz lubaczewski wrote: On Fri, Jan 27, 2012 at 03:00:24PM -0800, Adrian Klaver wrote: On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote: hiu I have weird situation. pg 9.1.2, compilet by our own script from source, on 10+ machines. on fours machines, pg_dump -s - dumps with data!: Are those 4 machines different from the other 6+? no idea. same os, same installation of pg. So much for that idea:) What does the script do? the compilation? just runs ./conmfigure with some options, make and make install. Not sure that it makes a difference, but on the chance it does, what are the options and are they the same for all machines? I am guessing you have not seen this in previous versions of postgres? that's the first time I saw this. and we never had older pg on thess machines. there is some suggestion that it might be related to extensions ... but I am not sure what/how to check. I am not going to much help here, as I am still learning the extension mechanism. For the sake of others that might have a clue, what are the extensions involved? Also, are all the tables having their data dumped or only those that relate to extensions? Best regards, depesz -- Adrian Klaver adrian.kla...@gmail.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] pg_dump -s dumps data?!
On Fri, Jan 27, 2012 at 03:00:24PM -0800, Adrian Klaver wrote: > On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote: > >hiu > >I have weird situation. > >pg 9.1.2, compilet by our own script from source, on 10+ machines. > >on fours machines, pg_dump -s - dumps with data!: > > Are those 4 machines different from the other 6+? no idea. same os, same installation of pg. > What does the script do? the compilation? just runs ./conmfigure with some options, make and make install. > I am guessing you have not seen this in previous versions of postgres? that's the first time I saw this. and we never had older pg on thess machines. there is some suggestion that it might be related to extensions ... but I am not sure what/how to check. Best regards, 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
Re: [GENERAL] pg_dump -s dumps data?!
On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote: hiu I have weird situation. pg 9.1.2, compilet by our own script from source, on 10+ machines. on fours machines, pg_dump -s - dumps with data!: Are those 4 machines different from the other 6+? What does the script do? I am guessing you have not seen this in previous versions of postgres? postgres@machine:~$ pg_dump --verbose --schema-only dbname> q ... pg_dump: creating TABLE x1 pg_dump: restoring data for table "x2" pg_dump: dumping contents of table x2 pg_dump: restoring data for table "x3" pg_dump: dumping contents of table x3 ... What could be wrong? Same pg_dump call on the same host, but for different database dumps just schema!? Best regards, depesz -- Adrian Klaver adrian.kla...@gmail.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] pg_dump -s dumps data?!
hiu I have weird situation. pg 9.1.2, compilet by our own script from source, on 10+ machines. on fours machines, pg_dump -s - dumps with data!: postgres@machine:~$ pg_dump --verbose --schema-only dbname > q ... pg_dump: creating TABLE x1 pg_dump: restoring data for table "x2" pg_dump: dumping contents of table x2 pg_dump: restoring data for table "x3" pg_dump: dumping contents of table x3 ... What could be wrong? Same pg_dump call on the same host, but for different database dumps just schema!? Best regards, 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
Re: [GENERAL] error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
Il giorno ven, 27/01/2012 alle 12.38 -0500, Tom Lane ha scritto: > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > > "bcbydocinfo_pk" for table "barcodebydocumentinfo" > > ERROR: permission denied for relation documents > > This example works for me. Are you sure you are executing the CREATE > TABLE command as user "neos"? Until ten minutes ago I was sure about it, but I was wrong. I was writing to the list about it when I read your message. Sorry for the noise. Thank, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] populating database, partition table, foreign key constraint Error
Hello, I have a problem about dropping and recovering foreign key constraint. Since we are using table partitioning for table A, records are always directed to one partition by triggers, leaving the parent table A empty. However, for table B where A's id serves as a foreign key, the foreign key constraint tells the database to look into the parent table (A), which is empty. This will cause the violation of FK-constraint when inserting into table B. The previous way to solve this problem is by issuing commands like: ALTER table exon_exon_junc_obs disable trigger all ; It doesn't work in another server where I am not the superuser. I have tried to use ""ALTER table table_B_name DROP CONSTRAINT constraint_name;""" It worked but I can not recover the foreign key constraint after inserting rows. The command """ALTER table table table_B_name ADD CONSTRAINT constraint_name FOREIGN KEY (A_id) REFERENCES A(A_id)""" returns ERROR: insert or update on table "B" violates foreign key constraint "constraint_name" DETAIL: Key (A_id)=(1) is not present in table "A". Have you ever encountered a similar problem? Any possible solutions to it? Thank you in advance! Xiaoning -- 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] error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
Giuseppe Sacco writes: > I get this error while executing a CREATE TABLE statement. > This is my CREATE statement: > CREATE TABLE agenzia.BarcodeByDocumentInfo ( > docId VARCHAR(17) NOT NULL, > defaultOp VARCHAR(10) NOT NULL DEFAULT 'Append', > CONSTRAINT BcByDocInfo_pk PRIMARY KEY (docId), > CONSTRAINT BcByDoc_defOp_ck > CHECK ( defaultOp = 'Append' OR defaultOp = 'Overwrite' ), > CONSTRAINT BcByDoc_docId_fk FOREIGN KEY(docId) > REFERENCES agenzia.Documents(docId) > ); > When I execute it on postgresql 9.0.6 I get this messages: > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "bcbydocinfo_pk" for table "barcodebydocumentinfo" > ERROR: permission denied for relation documents This example works for me. Are you sure you are executing the CREATE TABLE command as user "neos"? 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] error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
Il giorno ven, 27/01/2012 alle 08.54 -0800, Adrian Klaver ha scritto: > On Friday, January 27, 2012 8:25:56 am Giuseppe Sacco wrote: [...] > > I am owner of table "documents": > > > > neos=> \dt agenzia.documents > > List of relations > > Schema | Name| Type | Owner > > -+---+---+--- > > agenzia | documents | table | neos > > (1 row) > > > > I read the documentation about postgresql 9.0 and it seems the error > > message is about permission "x". As you may see "x" is among my > > permissions: > > The x(REFERENCES) permission needs to be on both tables for the owner of the > referenced table(noes). Well, I am owner of the referenced table. I cannot check anything on the barcodebydocumentinfo table since it is the one I am trying to CREATE. Thanks, Giuseppe -- 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] error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
On Friday, January 27, 2012 8:25:56 am Giuseppe Sacco wrote: > Hi, > I get this error while executing a CREATE TABLE statement. > This is my CREATE statement: > > CREATE TABLE agenzia.BarcodeByDocumentInfo ( > docId VARCHAR(17) NOT NULL, > defaultOp VARCHAR(10) NOT NULL DEFAULT 'Append', > CONSTRAINT BcByDocInfo_pk PRIMARY KEY (docId), > CONSTRAINT BcByDoc_defOp_ck > CHECK ( defaultOp = 'Append' OR defaultOp = 'Overwrite' ), > CONSTRAINT BcByDoc_docId_fk FOREIGN KEY(docId) > REFERENCES agenzia.Documents(docId) > ); > > When I execute it on postgresql 9.0.6 I get this messages: > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "bcbydocinfo_pk" for table "barcodebydocumentinfo" ERROR: permission > denied for relation documents > > > I am owner of table "documents": > > neos=> \dt agenzia.documents > List of relations > Schema | Name| Type | Owner > -+---+---+--- > agenzia | documents | table | neos > (1 row) > > I read the documentation about postgresql 9.0 and it seems the error > message is about permission "x". As you may see "x" is among my > permissions: The x(REFERENCES) permission needs to be on both tables for the owner of the referenced table(noes). This is what I got from the thread below: http://archives.postgresql.org/pgsql-general/2011-02/msg00957.php So see what your permissions are on for table barcodebydocumentinfo. Also who the owner of barcodebydocumentinfo is. > > neos=> \dp agenzia.documents >Access privileges > Schema | Name| Type | Access privileges | Column access > privileges > -+---+---+--+- > - agenzia | documents | table | neos=arwdDxt/neos +| > > | | | agenzia_r=arwdt/neos | > > (1 row) > > Do you have suggestion about this problem? > > I thank you very much, > Giuseppe -- Adrian Klaver adrian.kla...@gmail.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] error "permission denied for relation" on postgresql 9.0.6 during CREATE TABLE
Hi, I get this error while executing a CREATE TABLE statement. This is my CREATE statement: CREATE TABLE agenzia.BarcodeByDocumentInfo ( docId VARCHAR(17) NOT NULL, defaultOp VARCHAR(10) NOT NULL DEFAULT 'Append', CONSTRAINT BcByDocInfo_pk PRIMARY KEY (docId), CONSTRAINT BcByDoc_defOp_ck CHECK ( defaultOp = 'Append' OR defaultOp = 'Overwrite' ), CONSTRAINT BcByDoc_docId_fk FOREIGN KEY(docId) REFERENCES agenzia.Documents(docId) ); When I execute it on postgresql 9.0.6 I get this messages: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bcbydocinfo_pk" for table "barcodebydocumentinfo" ERROR: permission denied for relation documents So, if I understand correctly the error message, this is a missing permission a table "documents" that is only used in my CREATE STATEMENT on a FOREIGN KEY constraint. This is table "documents": neos=> \d agenzia.documents Table "agenzia.documents" Column | Type | Modifiers +---+-- docid | character varying(17) | not null description| character varying(45) | protid | character varying(50) | iscommondata | character(5) | not null default 'FALSE'::bpchar tobecrypted| character(5) | not null default 'FALSE'::bpchar islistofvalues | character(5) | not null default 'FALSE'::bpchar isfulltext | character(5) | not null default 'FALSE'::bpchar Indexes: [...] Check constraints: [...] Foreign-key constraints: [...] Referenced by: [...] I am owner of table "documents": neos=> \dt agenzia.documents List of relations Schema | Name| Type | Owner -+---+---+--- agenzia | documents | table | neos (1 row) I read the documentation about postgresql 9.0 and it seems the error message is about permission "x". As you may see "x" is among my permissions: neos=> \dp agenzia.documents Access privileges Schema | Name| Type | Access privileges | Column access privileges -+---+---+--+-- agenzia | documents | table | neos=arwdDxt/neos +| | | | agenzia_r=arwdt/neos | (1 row) Do you have suggestion about this problem? I thank you very much, Giuseppe -- 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 typecast an integer into a timestamp?
On Friday, January 27, 2012 7:44:55 am bbo...@free.fr wrote: > Hello! > > again quite a stupid problem i regularly run into > and that i still haven't solved yet... > > again i used a type timestamp to keep a track of modification time, and > again it gets stupid and confusing. > > first of all the errors are labeled as timestamp without timezone, i only > specified timestamp > > the data was created as a timestamp with php-mktime, but when sending to > the database postgres complains that its an int, and when i try to > typecast it, (with the ::timestamp appendix to the value), that its not > possible to convert an int to a timestamp (without timezone) . > > so as usual i would discard the timezone datatype and alter the table to > use integer instead, but this time i am wondering, since this datatype is > present, there's surely a way to use it properly? but how? > > please enlighten me! Did some digging. php-mktime returns the Unix epoch (seconds since January 1 1970 00:00:00 GMT) Postgres has a function(to_timestamp) that will convert that to a timestamp: http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html to_timestamp(double precision) timestamp with time zoneconvert Unix epoch to time stamp to_timestamp(1284352323) So something like the below in your query should work: to_timestamp(int_returned_from_php) > > ciao > Bruno -- Adrian Klaver adrian.kla...@gmail.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 typecast an integer into a timestamp?
On 1/27/2012 9:44 AM, bbo...@free.fr wrote:
Hello!
again quite a stupid problem i regularly run into
and that i still haven't solved yet...
again i used a type timestamp to keep a track of modification time, and again
it gets stupid and confusing.
first of all the errors are labeled as timestamp without timezone, i only
specified timestamp
the data was created as a timestamp with php-mktime, but when sending to the
database postgres complains that its an int, and when i try to typecast it,
(with the ::timestamp appendix to the value), that its not possible to convert
an int to a timestamp (without timezone) .
so as usual i would discard the timezone datatype and alter the table to use
integer instead, but this time i am wondering, since this datatype is present,
there's surely a way to use it properly? but how?
please enlighten me!
ciao
Bruno
The problem is that php mktime returns an integer. Not a date/time.
mktime returns the number of seconds since Jan 1 1970.
The best answer is to not use mktime. Find a php function that returns
a formatted string like strftime('%Y.%m.%d').
-Andy
--
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] Full Text Search, avoiding lexemes search
Daniel,
just use different fts configuration for search, which doesn't
includes stemmers.
Regards,
Oleg
On Fri, 27 Jan 2012, Daniel V?zquez wrote:
Hi guys!
Full text search, searches by lexemes, this minds that if you are finding
for "gato" word you are really finding for {gat} lexeme.
I you construct vectors for the words "gato", "gatos", "gata", "gatas", all
have the same lexema {gat}
Then the search "gato" that is to say the search {gat} matches with all
previous vectors.
There some way (configuration, query) to match only for "gato" and
avoid "gatos" "gata" "gatas", with FTS ??
Or match only for "gato" "gatos" buy no for "gata" "gatas"?
Tnks!
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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 typecast an integer into a timestamp?
On Friday, January 27, 2012 7:44:55 am bbo...@free.fr wrote: > Hello! > > again quite a stupid problem i regularly run into > and that i still haven't solved yet... > > again i used a type timestamp to keep a track of modification time, and > again it gets stupid and confusing. > > first of all the errors are labeled as timestamp without timezone, i only > specified timestamp http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html " Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) " > > the data was created as a timestamp with php-mktime, but when sending to > the database postgres complains that its an int, and when i try to > typecast it, (with the ::timestamp appendix to the value), that its not > possible to convert an int to a timestamp (without timezone) . Alter the field to be timestamp with time zone and see if that helps. FYI if you want to cast to timestamp with time zone, use ::timestamptz > > so as usual i would discard the timezone datatype and alter the table to > use integer instead, but this time i am wondering, since this datatype is > present, there's surely a way to use it properly? but how? > > please enlighten me! > > ciao > Bruno -- Adrian Klaver adrian.kla...@gmail.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] How to typecast an integer into a timestamp?
Hello! again quite a stupid problem i regularly run into and that i still haven't solved yet... again i used a type timestamp to keep a track of modification time, and again it gets stupid and confusing. first of all the errors are labeled as timestamp without timezone, i only specified timestamp the data was created as a timestamp with php-mktime, but when sending to the database postgres complains that its an int, and when i try to typecast it, (with the ::timestamp appendix to the value), that its not possible to convert an int to a timestamp (without timezone) . so as usual i would discard the timezone datatype and alter the table to use integer instead, but this time i am wondering, since this datatype is present, there's surely a way to use it properly? but how? please enlighten me! ciao Bruno -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full Text Search, avoiding lexemes search
Hi guys!
Full text search, searches by lexemes, this minds that if you are finding
for "gato" word you are really finding for {gat} lexeme.
I you construct vectors for the words "gato", "gatos", "gata", "gatas", all
have the same lexema {gat}
Then the search "gato" that is to say the search {gat} matches with all
previous vectors.
There some way (configuration, query) to match only for "gato" and
avoid "gatos" "gata" "gatas", with FTS ??
Or match only for "gato" "gatos" buy no for "gata" "gatas"?
Tnks!
[GENERAL] Full Text Search, avoiding lexemes search
Hi guys!
Full text search, searches by lexemes, this minds that if you are finding
for "gato" word you are really finding for {gat} lexeme.
I you construct vectors for the words "gato", "gatos", "gata", "gatas", all
have the same lexema {gat}
Then the search "gato" that is to say the search {gat} matches with all
previous vectors.
There some way (configuration, query) to match only for "gato" and
avoid "gatos" "gata" "gatas", with FTS ??
Or match only for "gato" "gatos" buy no for "gata" "gatas"?
Tnks!
[GENERAL] Fwd: [SQL] Query question
Didn't reply-all Begin forwarded message: > From: David Johnston > Date: January 27, 2012 9:01:37 EST > To: John Tuliao > Subject: Re: [SQL] Query question > > On Jan 26, 2012, at 7:00, John Tuliao wrote: > >> I seem to have a problem with a specific query: >> >> The inside query seems to work on it's own: >> >> select prefix >> from john_prefix >> where strpos(jpt_test.number,john_prefix.prefix) = '1' >> order by char_length(john_prefix.prefix) desc limit 1 >> >> but when I execute it with this: >> >> UPDATE >> jpt_test >> set >> number = substring(number from length(john_prefix.prefix)+1) >> from >> john_prefix >> where >> prefix in ( >> select prefix >> from john_prefix >> where strpos(jpt_test.number,john_prefix.prefix) = '1' >> order by char_length(john_prefix.prefix) desc limit 1 >> ) ; >> >> table contents are as follows >> >> john_prefix table: >> >> prefix >> - >> 123 >> 234 >> >> jpt_test table: >> >> number >> --- >> 123799 >> 023499 <<< supposed to have no match >> 234999 >> >> Am I missing something here? Any help will be appreciated. >> >> Regards, >> JPT >> >> > > Your double-use of john_prefix is problematic; combined with the use of a > sub-query in the where clause. When you use from with update you need to > specify how the from table and the update table are related - you have not > done this since the sub-query from reference is not the same as the from > clause table reference. > > David J.
Re: [GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
Hi > "DK" == Dmitry Koterov writes: DK> create table a(i integer); DK> CREATE UNIQUE INDEX a_idx ON a USING btree (i); DK> CREATE FUNCTION a_tr() RETURNS trigger AS DK> $body$ DK> BEGIN DK> DELETE FROM a WHERE i = NEW.i; DK> RETURN NEW; DK> END; DK> $body$ DK> LANGUAGE 'plpgsql'; DK> CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE DK> a_tr(); The DELETE doesn't see the row the other transaction inserted and doesn't delete anything (and doesn't block). This happens later when the row is inserted and the index is updated. You can try the insert and catch the unique violation in a loop (see http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html) although that won't work with a BEFORE trigger. Regards, Julian -- Julian v. Bock Projektleitung Software-Entwicklung OpenIT GmbH Tel +49 211 239 577-0 In der Steele 33a-41 Fax +49 211 239 577-10 D-40599 Düsseldorf http://www.openit.de HRB 38815 Amtsgericht Düsseldorf USt-Id DE 812951861 Geschäftsführer: Oliver Haakert, Maurice Kemmann -- 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] Don't Thread On Me (PostgreSQL related)
On Fri, Jan 27, 2012 at 1:28 AM, Eduardo Morras wrote: > At 00:32 27/01/2012, you wrote: > > There are cases where intraquery parallelism would be helpful. As far as >> I understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL) >> RDBMS which does not offer some sort of intraquery parallelism, and when >> running queries across very large databases, it might be helpful to be able >> to, say, scan different partitions simultaneously using different threads. >> So I think it is wrong to simply dismiss the need out of hand. The thing >> though is that I am not sure that where this need really comes to the fore, >> it is typical of single-server instances, and so this brings me to the >> bigger question. >> >> The question in my mind though is a more basic one: How should >> intraquery parallelism be handled? Is it something PostgreSQL needs to do >> or is it something that should be the work of an external project like >> Postgres-XC? Down the road is there value in merging the codebases, >> perhaps making stand-alone/data/coordination node a compile time option? >> > > I still don't think threads are the solution for this scenary. You can do > intraquery parallelism with multiprocess easier and safer than with > multithread. You launch a process with the whole query, it divide the work > in chunks and assigns them to different process instead of threads. You can > use shared resources for communicattion between process. When all work is > done, they pass results to the original process and it join them. The > principal advantage doing it with process is that if one of the child > subprocess dies, it can be killed/slained and relaunched without any damage > to the work of the other brothers, but if you use threads, the whole > process and all the work done is lost. > Well, I am assuming that when anything regarding a query crashes, the work for that query should be lost so I don't see that as a big issue provided that you still have one process per session. The larger issue would be rewriting the backend so that this is safe, and it would complicate QA. For this reason, I assume for now that this is not the way to go. > > It's not the unique advantage of using process vs threads. Some years ago, > one of the problems on multi socket servers was with the shared memory and > communications between the sockets. The inter cpu speed was too much slow > and latency too much high. Now, we have multi cpus in one socket and faster > intersocket communications and this is not a problem anymore. Even better, > the speed and latency communicating 2 or more servers (not sockets or cpus) > is reaching levels where a postgresql could have a shared memory between > them, for example using Hypertransport cards or modern FC, and it's easier, > lot easier, launch a remote process than a remote thread. But this gets back to my question: are there significant use cases where intraquery parallelism makes sense where clustering across servers does not? The reason I ask is that if there are not, then the work that's going into Postgres-XC would get us there entirely, in a multi-process (single-threaded), two tiered, network transparent model that would potentially scale up well. Best Wishes, Chris Travers
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
At 00:32 27/01/2012, you wrote: There are cases where intraquery parallelism would be helpful. As far as I understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL) RDBMS which does not offer some sort of intraquery parallelism, and when running queries across very large databases, it might be helpful to be able to, say, scan different partitions simultaneously using different threads. So I think it is wrong to simply dismiss the need out of hand. The thing though is that I am not sure that where this need really comes to the fore, it is typical of single-server instances, and so this brings me to the bigger question. The question in my mind though is a more basic one: How should intraquery parallelism be handled? Is it something PostgreSQL needs to do or is it something that should be the work of an external project like Postgres-XC? Down the road is there value in merging the codebases, perhaps making stand-alone/data/coordination node a compile time option? I still don't think threads are the solution for this scenary. You can do intraquery parallelism with multiprocess easier and safer than with multithread. You launch a process with the whole query, it divide the work in chunks and assigns them to different process instead of threads. You can use shared resources for communicattion between process. When all work is done, they pass results to the original process and it join them. The principal advantage doing it with process is that if one of the child subprocess dies, it can be killed/slained and relaunched without any damage to the work of the other brothers, but if you use threads, the whole process and all the work done is lost. It's not the unique advantage of using process vs threads. Some years ago, one of the problems on multi socket servers was with the shared memory and communications between the sockets. The inter cpu speed was too much slow and latency too much high. Now, we have multi cpus in one socket and faster intersocket communications and this is not a problem anymore. Even better, the speed and latency communicating 2 or more servers (not sockets or cpus) is reaching levels where a postgresql could have a shared memory between them, for example using Hypertransport cards or modern FC, and it's easier, lot easier, launch a remote process than a remote thread. Obviously such is not a question that needs to be addressed now. We can wait until someone has something that is production-ready and relatively feature-complete before discussing merging projects. Best Wishes, Chris Travers -- 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] Don't Thread On Me (PostgreSQL related)
On Fri, Jan 27, 2012 at 00:32, Chris Travers wrote: > > > On Thu, Jan 26, 2012 at 3:02 PM, Merlin Moncure wrote: >> >> On Thu, Jan 26, 2012 at 3:52 PM, Rodrigo E. De León Plicet >> wrote: >> > Quote: >> > >> > == >> > >> > This thread >> > >> > >> > http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5143643.html >> > >> > was mentioned in a performance sub-group posting. Give it a read. >> > >> > Back? It means, so far as I can see, that PG is toast. It will fall >> > down to being the cheap and dirty alternative to MySql, which even >> > has, at least two, multi-threaded engines. DB2 switched it's *nix >> > engine to threads from processes with release 9.5. Oracle claims it >> > for releases going back to 7 (I haven't tried to determine which parts >> > or applications; Larry has bought so many tchochtkes over the >> > years...). SQL Server is threaded. >> > >> > Given that cpu's are breeding threads faster than cores, >> > PG will fall into irrelevance. >> >> The author of that post apparently doesn't understand that even though >> postgresql hasn't 'switched to threads', it can still do more than one >> thing at once. Each process is itself an execution thread. A >> multi-threaded query planner is perfectly possible in postgresql >> architecture -- however each one must reside in it's own process and >> you have to use shared memory instead instead of pthreads and locking. >> Big whoop. The only thing at stake with a multi threaded planner is >> optimizing single user tasks which is, while important, a niche >> optimization. PostgreSQL is for more scalable than mysql for >> multi-user loads and the gap is increasing. >> >> > There are cases where intraquery parallelism would be helpful. As far as I > understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL) > RDBMS which does not offer some sort of intraquery parallelism, and when > running queries across very large databases, it might be helpful to be able > to, say, scan different partitions simultaneously using different threads. > So I think it is wrong to simply dismiss the need out of hand. The thing > though is that I am not sure that where this need really comes to the fore, > it is typical of single-server instances, and so this brings me to the > bigger question. Intraquery parallelism is certainly something PostgreSQL is in need of, and it's going to get more and more obvious over the next couple of years. Whether it uses threads or not is an implementation detail, just like processing of regular queries on threads or processes or pools is an implementation detail. So the lack of threads isn't a problem - the lack of intraquery parallelism is. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Help needed creating a view
Quoth "David Johnston" :
> A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false
> END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false
> END AS english_cmp FROM applications
> a) Expand to multiple columns and store either the default "false" or the
> value of "completed" into the value for the corresponding column
>
> B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS
> did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS
> did_english FROM "A" GROUP BY user_id
> b) Then determine whether the user_id has at least one "true" in the given
> column by using the "bool_or" function
>
> Dynamic columns are difficult to code in SQL. You should probably also
> include some kind of "OTHER COMPLETED DISCIPLINES" column to catch when you
> add an previously unidentified course - "course_name NOT IN
> ('Maths','English','...')"
>
> Also concerned with the fact that, as coded, a single complete course
> triggers the given flag. What happens when you want to specify that they
> have only completed 3 of 4 courses? Also, instead of hard-coding the
> "course_name" targets you may want to do something like "CASE WHEN
> course_name IN (SELECT course_name FROM courses WHERE course_type =
> 'Maths')".
Many thanks David for a clear and comprehensive reply, although I haven't
completely grokked your use of bool_or.
No matter though, because 'CASE WHEN ... THEN END' is precisely
the idiom I was looking for.
My view definition now looks something like this:
CREATE VIEW alumni AS
SELECT * FROM (
-- query includes every user_id in applications
SELECT user_id,
CASE WHEN course_name='Maths' THEN completed END AS
maths_alumni,
CASE WHEN course_name='English' THEN completed END AS
english_alumni,
...
...
FROM applications ) AS foo
-- so we need to exclude user_ids who did not complete *any* courses
WHERE maths_alumni IS TRUE
OR english_alumni IS TRUE
...
...;
Thanks again.
Sebastian
--
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
