Re: [GENERAL] Help needed creating a view
Quoth David Johnston pol...@yahoo.com:
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 column_name 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
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
On Fri, Jan 27, 2012 at 00:32, Chris Travers chris.trav...@gmail.com wrote: On Thu, Jan 26, 2012 at 3:02 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Jan 26, 2012 at 3:52 PM, Rodrigo E. De León Plicet rdele...@gmail.com 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] 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 1:28 AM, Eduardo Morras nec...@retena.com 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] Stange duplicate key value violates unique constraint after delete at ON UPDATE trigger
Hi DK == Dmitry Koterov dmi...@koterov.ru 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
[GENERAL] Fwd: [SQL] Query question
Didn't reply-all Begin forwarded message: From: David Johnston pol...@yahoo.com Date: January 27, 2012 9:01:37 EST To: John Tuliao jptul...@htechcorp.net Subject: Re: [SQL] Query question On Jan 26, 2012, at 7:00, John Tuliao jptul...@htechcorp.net 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.
[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] 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
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
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 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] 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
[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] 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
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
Giuseppe Sacco giuse...@eppesuigoccas.homedns.org 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
[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
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] 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 database_name - 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] 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 -sdatabase_name - 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
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 -sdatabase_name - 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 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 -sdatabase_name - 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?!
hubert depesz lubaczewski dep...@depesz.com 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 database_name - 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
