Re: [GENERAL] Strange problem with create table as select * from table;
On Friday, November 04, 2011 3:43:48 pm hubert depesz lubaczewski wrote: > On Fri, Nov 04, 2011 at 05:49:44PM -0400, Tom Lane wrote: > > You said that pg_dump does not show the corruption. That could be > > because the data is coming out through the COPY code path instead of > > the SELECT code path. Could you try a pg_dump with --inserts (which > > will fetch the data with SELECTs) and see if it shows corrupt data? > > i'm running the pg_dump (it will take some time, so don't hold your > breath), but at the same time - I can select these rows, correctly, with > normal SELECT from table (xobjects table). Doesn't it disprove this > theory? Another question. Between 07/20/11 and this recent attempt did you do a CREATE TABLE AS on this table and not have corrupted rows? > > 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] Strange problem with create table as select * from table;
On Fri, Nov 04, 2011 at 05:49:44PM -0400, Tom Lane wrote: > You said that pg_dump does not show the corruption. That could be > because the data is coming out through the COPY code path instead of > the SELECT code path. Could you try a pg_dump with --inserts (which > will fetch the data with SELECTs) and see if it shows corrupt data? pg_dump finished. all ids are correct - i.e. there are no rows with xobject_id = -1. 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] Strange problem with create table as select * from table;
On Fri, Nov 04, 2011 at 05:06:35PM -0700, Adrian Klaver wrote: > Another question. > Between 07/20/11 and this recent attempt did you do a CREATE TABLE AS on this > table and not have corrupted rows? don't remember. 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
[GENERAL] Function Question - Inserting into a table with foreign constraints
Hello all, Could someone give me an example as to how I would accomplish something like this with a function?: 3 tables: tableA: id (serial), name (varchar), description (varchar), subcat_id (integer) tableB: id (serial), subcat_name (varchar), cat_id (integer) tableC: id (serial), cat_name I would like to create a function (ie. stored procedure) that I can pass 4 things: name, description, subcat_name, cat_name When the procedure runs it would do the following: 1. Check to see if cat_name exists in tableC a. if so, get the id b. if not, insert a new record into tableC using the supplied cat_name, and get the id of the newly created record 2. Check to see if subcat_name exists in tableB where cat_id is the value returned from step 1 a. if so, get the id b. if not, insert a new record into tableB using the supplied subcat_name and the cat_id returned from step 1, and get the id of the newly created record 3. Insert a record into tableA with the name and description supplied to the procedure, and the subcat_id returned from step 2 In the end, when my app calls the procedure I'd like it to automatically create records in tables tableC and tableB if the _name fields don't already exist, then insert the primary record into tableA using the foreign key IDs from the other table. I'd like to do it this way because cat_name is unique in tableC, and (subcat_name, cat_id) are singularly unique in tableB. (ie. there can be multiple subcat_names as long as they belong to different categories from tableC). Any help would be greatly appreciated. Thanks, Brandon -- 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] Function Question - Inserting into a table with foreign constraints
On 05/11/2011 04:34, Brandon Phelps wrote: > Hello all, > > Could someone give me an example as to how I would accomplish something > like this with a function?: > > 3 tables: > > tableA: id (serial), name (varchar), description (varchar), subcat_id > (integer) > tableB: id (serial), subcat_name (varchar), cat_id (integer) > tableC: id (serial), cat_name > > I would like to create a function (ie. stored procedure) that I can pass > 4 things: > name, description, subcat_name, cat_name To begin with, don't give the parameters the same names as columns in the tables you're going to be manipulating. create or replace function my_function( p_name varchar, p_description varchar, p_subcat_name varchar, p_cat_name varchar ) returns void as $$ declare m_cat_id integer; m_subcat_id integer; begin . (see below) return; end; $$ language plpgsql; > When the procedure runs it would do the following: > 1. Check to see if cat_name exists in tableC > a. if so, get the id > b. if not, insert a new record into tableC using the supplied > cat_name, and get the id of the newly created record Assuming you've read up[1] on how to create a pl/pgsql function in the first place, declare variables, etc, it'd go something like this: select id into m_cat_id from tablec where cat_name = p_cat_name; if not found then insert into tablec (cat_name) values (p_cat_name) returning id into m_cat_id; end if; Remember too that identifiers always fold to lower-case unless you double-quote them. > 2. Check to see if subcat_name exists in tableB where cat_id is the > value returned from step 1 > a. if so, get the id > b. if not, insert a new record into tableB using the supplied > subcat_name and the cat_id returned from step 1, and get the id of the > newly created record Similar to above, but store the value in m_subcat_id. > 3. Insert a record into tableA with the name and description supplied to > the procedure, and the subcat_id returned from step 2 insert into tablea (name, description, subcat_id) values (p_name, p_description, m_subcat_id); HTH, Ray. [1] http://www.postgresql.org/docs/9.1/static/plpgsql.html -- 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] Function Question - Inserting into a table with foreign constraints
On 11/5/2011 10:35 AM, Raymond O'Donnell wrote: On 05/11/2011 04:34, Brandon Phelps wrote: Hello all, Could someone give me an example as to how I would accomplish something like this with a function?: 3 tables: tableA: id (serial), name (varchar), description (varchar), subcat_id (integer) tableB: id (serial), subcat_name (varchar), cat_id (integer) tableC: id (serial), cat_name I would like to create a function (ie. stored procedure) that I can pass 4 things: name, description, subcat_name, cat_name To begin with, don't give the parameters the same names as columns in the tables you're going to be manipulating. create or replace function my_function( p_name varchar, p_description varchar, p_subcat_name varchar, p_cat_name varchar ) returns void as $$ declare m_cat_id integer; m_subcat_id integer; begin . (see below) return; end; $$ language plpgsql; When the procedure runs it would do the following: 1. Check to see if cat_name exists in tableC a. if so, get the id b. if not, insert a new record into tableC using the supplied cat_name, and get the id of the newly created record Assuming you've read up[1] on how to create a pl/pgsql function in the first place, declare variables, etc, it'd go something like this: select id into m_cat_id from tablec where cat_name = p_cat_name; if not found then insert into tablec (cat_name) values (p_cat_name) returning id into m_cat_id; end if; Remember too that identifiers always fold to lower-case unless you double-quote them. 2. Check to see if subcat_name exists in tableB where cat_id is the value returned from step 1 a. if so, get the id b. if not, insert a new record into tableB using the supplied subcat_name and the cat_id returned from step 1, and get the id of the newly created record Similar to above, but store the value in m_subcat_id. 3. Insert a record into tableA with the name and description supplied to the procedure, and the subcat_id returned from step 2 insert into tablea (name, description, subcat_id) values (p_name, p_description, m_subcat_id); HTH, Ray. [1] http://www.postgresql.org/docs/9.1/static/plpgsql.html Thanks for the quick reply Ray. I had the notion of using IF statements to check if the IDs in question existed already, but I figured there might be a more fluid way of doing something like this without having a bunch of extra logic. With the method you outlined will I notice any huge performance impacts? The application would be parsing incoming data from another 3rd party application and could, at times, be executing the function in very fast succession, although never twice at the exact same moment (single threaded application, pending events will just block until they're up). Thanks again! -- Brandon -- 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] Excessive planner time for some queries with high statistics
On Sat, Nov 5, 2011 at 1:26 AM, Tom Lane wrote: > Stuart Bishop writes: >> We also found this problem did not occur on one of our staging >> systems, which had a default statistics target of 100. Lowering the >> statistics on the relavant columns from 1000 to 100 and reanalyzing >> made the overhead unnoticeable. > > eqjoinsel() is O(N^2) in the number of entries in the MCV lists. > I wouldn't expect this to be an issue unless comparison is pretty > expensive, but maybe those are string not integer columns? No, per http://paste.ubuntu.com/726193/ there are only integer columns being used. >> Thoughts on IRC was this might be a regression in 8.4.9, but I haven't >> got earlier versions to test with at the moment. > > eqjoinsel has worked like that for many years. Are you claiming you > didn't see this behavior in a prior release? If so, which one? I'm repeating speculation from discussions on IRC with pg devs in my timezone. I haven't tested with earlier versions. I can't confirm if this problem appeared with 8.4.9 or not - it is only recently that our query times got to the stage where we can start worrying about milliseconds instead of seconds :-) Previously, we have never noticed the planner overhead so had set default_statistics_target to 1000 to minimize the chance of bad plans due to a skewed sample. 150ms+ seems excessive though in this simple case. -- Stuart Bishop http://www.stuartbishop.net/ -- 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] Function Question - Inserting into a table with foreign constraints
On Nov 5, 2011, at 10:46, Brandon Phelps wrote: > On 11/5/2011 10:35 AM, Raymond O'Donnell wrote: >> On 05/11/2011 04:34, Brandon Phelps wrote > > With the method you outlined will I notice any huge performance impacts? The > application would be parsing incoming data from another 3rd party application > and could, at times, be executing the function in very fast succession, > although never twice at the exact same moment (single threaded application, > pending events will just block until they're up). > > Thanks again! > You would probably be much better off importing the third-party data into a staging table, performing all of your "key" creation, and then inserting the data into your final tables. While triggers would work you end up with a lot of pointless effort when in most cases keys are likely to exist. David J. -- 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 find owning schema in function
8.1+ database contains separate schemas for every company named company1, company2, companyi. order tables in those schemas contain trigger like for company1: CREATE OR REPLACE FUNCTION dok_seq_trig() RETURNS "trigger" AS $$BEGIN IF NEW.tasudok IS NULL AND NEW.doktyyp!='O' THEN NEW.tasudok = nextval( 'company1.'|| TG_RELNAME || '_'|| NEW.doktyyp ||'_seq'); END IF; IF NEW.arvenumber IS NULL AND NEW.doktyyp='O' THEN NEW.arvenumber = nextval( 'company1.'|| TG_RELNAME || '_'|| NEW.doktyyp ||'_seq'); END IF; RETURN NEW; END$$ LANGUAGE plpgsql STRICT; This code has hard coded schema name 'company1' . If new company schema n is created from existing one, trigger functions needs manual update to change schema to companyn. How to change this code so that instead of hard-coded schema name it automatically uses the schema where trigger function is defined ? -- 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] Function Question - Inserting into a table with foreign constraints
On 05/11/2011 14:46, Brandon Phelps wrote: > > With the method you outlined will I notice any huge performance > impacts? The application would be parsing incoming data from another > 3rd party application and could, at times, be executing the function in > very fast succession, although never twice at the exact same moment > (single threaded application, pending events will just block until > they're up). I honestly don't know... your best bet would be to benchmark and see. I'd guess that any changes to the code within the function would have less effect than local conditions - disk I/O, processor speed, etc. AIUI, putting your code into a function has the advantage that the Potgres only has to load pl/pgsql and parse the function once for any given connection, and then the plan is reused for all subsequent invocations coming from that connection. If the function is only going to be called once per connection, then you have the overhead of loading pl/pgsql each time it is called, on top of parsing the function; so you might be better off trying to do this outside of a function altogether. 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] Strange problem with create table as select * from table;
On Friday, November 04, 2011 6:04:02 pm Tom Lane wrote: > I wrote: > > A different line of thought is that there's something about these > > specific source rows, and only these rows, that makes them vulnerable to > > corruption during INSERT/SELECT. Do they by any chance contain any > > values that are unusual elsewhere in your table? One thing I'm > > wondering about right now is the nulls bitmap --- so do these rows have > > nulls (or not-nulls) in any place that's unusual elsewhere? > > Hah ... I have a theory. > > > This is trivial to fix, now that we know there's a problem --- the > function is only using that assumption to save itself a couple lines > of code. Penny wise, pound foolish :-( I killed a few brain cells just reading the explanation:) > > regards, tom lane > > -- 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] Strange problem with create table as select * from table;
I wrote:
> A different line of thought is that there's something about these
> specific source rows, and only these rows, that makes them vulnerable to
> corruption during INSERT/SELECT. Do they by any chance contain any
> values that are unusual elsewhere in your table? One thing I'm
> wondering about right now is the nulls bitmap --- so do these rows have
> nulls (or not-nulls) in any place that's unusual elsewhere?
Hah ... I have a theory.
I will bet that you recently added some column(s) to the source table
using ALTER TABLE ADD COLUMN and no default value, so that the added
columns were nulls and no table rewrite happened. And that these
troublesome rows predate that addition, but contained no nulls before
that. And that they are the only rows that, in addition to the above
conditions, contain data fields wide enough to require out-of-line
toasting.
These conditions together are enough to break the assumption in
toast_insert_or_update that the old and new tuples must have the same
value of t_hoff. But it can only happen when the source tuple is an
original on-disk tuple, which explains why only INSERT ... SELECT *
causes the problem, not any variants that require projection of a new
column set. When it does happen, toast_insert_or_update correctly
computes the required size of the new tuple ... but then it tells
heap_fill_tuple to fill the data part at offset olddata->t_hoff, which
is wrong (too small) and so the nulls bitmap that heap_fill_tuple
concurrently constructs will overwrite the first few data bytes. In
your example, the table contains 49 columns so the nulls bitmap requires
7 bytes, just enough to overwrite the first 6 data bytes as observed.
(In fact, given the values we see being filled in, I can confidently say
that you have two added-since-creation null columns, no more, no less.)
I can reproduce the problem with the attached test case (using the
regression database). With asserts enabled, the
Assert(new_len == olddata->t_hoff);
fails. With asserts off, corrupt data.
This is trivial to fix, now that we know there's a problem --- the
function is only using that assumption to save itself a couple lines
of code. Penny wise, pound foolish :-(
regards, tom lane
drop table wide;
create table wide as
select
ten as firstc,
unique1 as unique1_1,
unique2 as unique2_1,
two as two_1,
four as four_1,
ten as ten_1,
twenty as twenty_1,
hundred as hundred_1,
thousand as thousand_1,
twothousand as twothousand_1,
fivethous as fivethous_1,
tenthous as tenthous_1,
odd as odd_1,
even as even_1,
stringu1 as stringu1_1,
stringu2 as stringu2_1,
string4 as string4_1,
unique1 as unique1_2,
unique2 as unique2_2,
two as two_2,
four as four_2,
ten as ten_2,
twenty as twenty_2,
hundred as hundred_2,
thousand as thousand_2,
twothousand as twothousand_2,
fivethous as fivethous_2,
tenthous as tenthous_2,
odd as odd_2,
even as even_2,
stringu1 as stringu1_2,
stringu2 as stringu2_2,
string4 as string4_2,
unique1 as unique1_3,
unique2 as unique2_3,
two as two_3,
four as four_3,
ten as ten_3,
twenty as twenty_3,
hundred as hundred_3,
thousand as thousand_3,
twothousand as twothousand_3,
fivethous as fivethous_3,
tenthous as tenthous_3,
odd as odd_3,
even as even_3,
repeat('xyzzyxydlkadlkndvlelfzzy', 2) as widec
from onek limit 10;
alter table wide add column nullc1 int;
alter table wide add column nullc2 int;
drop table widec;
create table widec as select * from wide;
select firstc, to_hex(unique1_1), unique2_1, to_hex(unique1_2) from widec;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange problem with create table as select * from table;
On Fri, Nov 04, 2011 at 09:04:02PM -0400, Tom Lane wrote: > Hah ... I have a theory. > > I will bet that you recently added some column(s) to the source table > using ALTER TABLE ADD COLUMN and no default value, so that the added > columns were nulls and no table rewrite happened. And that these > troublesome rows predate that addition, but contained no nulls before > that. And that they are the only rows that, in addition to the above > conditions, contain data fields wide enough to require out-of-line > toasting. > > These conditions together are enough to break the assumption in > toast_insert_or_update that the old and new tuples must have the same > value of t_hoff. Wow! Good catch. > This is trivial to fix, now that we know there's a problem --- the > function is only using that assumption to save itself a couple lines > of code. Penny wise, pound foolish :-( No doubt the assumption was true when the code was written, but still. Hve a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Foreign Keys and Deadlocks
Hi David, On Thu, 2011-11-03 at 15:30 -0700, David Kerr wrote: > I suspect that it has to be a transaction, and that further up in the TX is > an update to one of > the reference tables in each TX. This is your cause - updating the referenced table in the same transaction. That will want an exclusive lock on the row, but the shared lock taken by the foreign key check (in another process) is conflicting, and will deadlock when the other process will also want to update some row in the referenced table which is locked by a foreign key check in this process. While the lock on the referenced row was changed to be a shared lock instead of an exclusive lock as in older postgres versions (see http://archives.postgresql.org/pgsql-general/2002-11/msg00397.php for the original problem, which is relaxed now), the lock is still too strong and the deadlock problem remains. A solution is not trivial at all, and involves only locking the row for changes of the referenced columns (which postgres can't do currently). While getting rid of the foreign key will solve your problem, I think it's not the best solution - you can perhaps design a way to not update the referenced tables in the same transaction. Here we adopted a different solution - we run a patched postgres which skips that lock altogether, which means a partially broken foreign key code which mostly works but can leave orphans. I will not recommend to do that though - the reasons we did it that way is that it was the path of least resistance as the application was also running on other DBs (which were the primary DB at that time) and there was no way to make extensive changes to the application code. If I were to change the code, I would have separated the updated fields from the parent table to yet another child table, and have the parent table never updated. That will still have some potential for deadlock (if you don't order the inserts/updates properly) but much less. Cheers, Csaba. -- 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] postgresql-9.1.1 PQescapeByteaConn() generating incorrectly quoted output.
On Wed, 2011-11-02 at 18:21 +, Tom Lane wrote: > Graham Murray writes: > > Since upgrading test systems to postgresql 9.1, I am seeing some > inserts > > to bytea fields giving errors such as "ERROR: invalid byte sequence > for > > encoding "UTF8": 0xf9" Where the insert is from a C program using > libpq > > and is of the form "insert into xxx values(E'%s')" where the value > is > > the return of PQescapeByteaConn(); > > That is incorrect coding. The result of PQescapeByteaConn has never > been meant to be put into an E'' literal. You might have managed to > get away with it so long as standard_conforming_strings was off in the > server, but it's on by default in 9.1. Thanks. I have now changed it (for the next application release) to use 'plain' not E'' strings. Originally I was using non-E strings, but I changed it because 9.0.x (with the default standard_conforming_strings=off) gave a warning suggesting that they should be E' escaped. smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Linker error VS2008 c++
Hi, I'm traing to read a row from a table using visual studio 2008 and libpq.
I'm having problem with getting a int from a querry result, below im pasting
my code
struct subjects_group
{
unsigned long id;
std::string name;
};
list QS_PQsql::getGroups()
{
list lista;
subjects_group temp;
char *iptr;
res = PQexec(conn, "SELECT * FROM subjects_group");
if( PQresultStatus(res) != PGRES_TUPLES_OK )
{
PQclear(res);
return lista;
}
iptr = PQgetvalue(res,0,0);
temp.id = ntohl(*(uint32_t*)iptr); //here is line that causing troubles
it
gives me a several linker error
temp.name = PQgetvalue(res,0,1);
lista.push_back(temp);
return lista;
}
the errors that i've received:
1>QS_PQsql.obj : error LNK2028: unresolved token (0A000357) "extern "C"
unsigned long __stdcall ntohl(unsigned long)" (?ntohl@@$$J14YGKK@Z)
referenced in function "public: class std::list > __clrcall QS_PQsql::getGroups(void)"
(?getGroups@QS_PQsql@@$$FQ$AAM?AV?$list@Usubjects_group@@V?$allocator@Usubjects_group@@@std@@@std@@XZ)
1>QS_PQsql.obj : error LNK2019: unresolved external symbol "extern "C"
unsigned long __stdcall ntohl(unsigned long)" (?ntohl@@$$J14YGKK@Z)
referenced in function "public: class std::list > __clrcall QS_PQsql::getGroups(void)"
(?getGroups@QS_PQsql@@$$FQ$AAM?AV?$list@Usubjects_group@@V?$allocator@Usubjects_group@@@std@@@std@@XZ)
1>C:\Users\Cin\Documents\Visual Studio 2008\Projects\QS
Queue\Debug\QSserver.exe : fatal error LNK1120: 2 unresolved externals
I needed a stdint.h header because of uint32_t and that header was missing
in vs2008 sa i take it form vs2010
Thanks in advance for any help
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Linker-error-VS2008-c-tp4962308p4962308.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] What is *wrong* with this query???
I give! I'm flummoxed! Here is what I have, 3 tables: schedule companybuilding status0 3 x active 4 x active 5 x active 3 x active 3 x active 3 x active In the end, I want to replace the building id's above. They start out with the non-informative value of '1'; company id name status 3 x active 4 y active 5 z active building id company name 1 3 A active 2 3 B active 3 3 C active 4 4 D active 5 4 E active 6 4 F active 7 5 G active 8 5 H active 9 5 Iactive So, every company has 3 schedules. Of the 3, I'd like to select the one with the lowest id. I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair. select schedule.id as sched_id, bld.id as bid from schedule left join company on schedule.company = company.id left join (select * from building where building.company = company.id order by id limit 1) as bld where schedule.status = 'active' and company.status = 'active' and bld.status = 'active'; I get a syntax error on the the "where". ERROR: syntax error at or near "where" at character …. LINE 6: where ^ If I leave out the where clause entirely, that's an error also, ERROR: syntax error at or near ";" at character …. LINE 5: … as bld ; ^ So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't going to tell me what. What am I missing?
[GENERAL] Custom Contraint Violation Errors
Hi, I'm looking for a way to create a custom, for our application parsable, error message on constraint violation. The perfect thing would be, having table names, schemas and primary keys in that error message. My first thought was to create a function which gets triggered by the constraint violation, which creates the custom error. But I have not found any information if this is possible to create a trigger on a constraint violation, and if yes, how could that be done? I would be glad, If someone could light me up on that issue. Thanks in advance and Kind Regards, Michael -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Distinct on a non-sort column
I am trying to write a query that selects recent submissions (sorted by submission_date) but only selects the most recent one for each user_id. example query: /select distinct on (user_id) * from stories order by date_submitted desc limit 10;/ However postgres will not allow me to filter out duplicate rows with distinct unless I sort on that column, which would product useless results for me. Group by seems to have similiar problems, plus the additional problem of wanting aggregate functions to be used. I even tried sorting in a subquery, but it still comes out sorted by user_id: /select distinct on (user_id) * from stories where sid in (select sid from stories order by date_submitted desc limit 10);/ How can I work around this limitation to get the results sorted the way I want, then have the duplicates removed? I can't be the only one running into this limitation, there must be some workaround.
Re: [GENERAL] What is *wrong* with this query???
See embedded note after “as bld” Dave From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Murphy Sent: Saturday, November 05, 2011 12:51 AM To: pgsql-general@postgresql.org Subject: [GENERAL] What is *wrong* with this query??? I give! I'm flummoxed! select schedule.id as sched_id, bld.id as bid from schedule left join company on schedule.company = company.id left join (select * from building where building.company = company.id order by id limit 1) as bld ** <-- LEFT JOIN “ON WHAT?” where schedule.status = 'active' and company.status = 'active' and bld.status = 'active';
Re: [GENERAL] What is *wrong* with this query???
On Friday, November 04, 2011 9:51:14 pm Steve Murphy wrote: > I give! I'm flummoxed! > > Here is what I have, 3 tables: > > schedule > companybuilding status0 > 3 x active > 4 x active > 5 x active > 3 x active > 3 x active > 3 x active > > In the end, I want to replace the building id's above. They start out with > the non-informative value of '1'; > > company > id name status > 3 x active > 4 y active > 5 z active > > > building > id company name > 1 3 A active > 2 3 B active > 3 3 C active > 4 4 D active > 5 4 E active > 6 4 F active > 7 5 G active > 8 5 H active > 9 5 Iactive > > So, every company has 3 schedules. Of the 3, I'd like to select the one > with the lowest id. > > I'm using postgresql 8.1. Yes, ancient, but I have no choice in this > affair. > > select schedule.id as sched_id, bld.id as bid > from > schedule > left join company on schedule.company = company.id > left join (select * from building where building.company = > company.id order by id limit 1) as bld where > schedule.status = 'active' and company.status = 'active' and > bld.status = 'active'; > > I get a syntax error on the the "where". > > ERROR: syntax error at or near "where" at character …. > LINE 6: where >^ > > If I leave out the where clause entirely, that's an error also, > ERROR: syntax error at or near ";" at character …. > LINE 5: … as bld ; > ^ semicolon> > > So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't > going to tell me what. What am I missing? on something=something_else -- 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] What is *wrong* with this query???
On Fri, Nov 4, 2011 at 10:51 PM, Steve Murphy wrote: > select schedule.id as sched_id, bld.id as bid > from > schedule > left join company on schedule.company = company.id > left join (select * from building where building.company = > company.id order by id limit 1) as bld > where > schedule.status = 'active' and company.status = 'active' and > bld.status = 'active'; Looks like you need an on clause after the second left join. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
On Sat, Nov 5, 2011 at 10:51 AM, Steve Murphy wrote: > > > I give! I'm flummoxed! > > > > Here is what I have, 3 tables: > > > > schedule > > company building status0 > > 3 x active > > 4 x active > > 5 x active > > 3 x active > > 3 x active > > 3 x active > > > > In the end, I want to replace the building id's above. They start out with > the non-informative value of '1'; > > > > company > > id name status > > 3 x active > > 4 y active > > 5 z active > > > > > > building > > id company name > > 1 3 A active > > 2 3 B active > > 3 3 C active > > 4 4 D active > > 5 4 E active > > 6 4 F active > > 7 5 G active > > 8 5 H active > > 9 5 I active > > > > So, every company has 3 schedules. Of the 3, I'd like to select the one with > the lowest id. > > > > I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair. > > > > select schedule.id as sched_id, bld.id as bid > > from > > schedule > > left join company on schedule.company = company.id > > left join (select * from building where building.company = > company.id order by id limit 1) as bld > > where > > schedule.status = 'active' and company.status = 'active' and > bld.status = 'active'; > > > > I get a syntax error on the the "where". > > > > ERROR: syntax error at or near "where" at character …. > > LINE 6: where > > ^ > > > > If I leave out the where clause entirely, that's an error also, > > ERROR: syntax error at or near ";" at character …. > > LINE 5: … as bld ; > > ^ > > > > So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't > going to tell me what. > > What am I missing? left join (select * from building where building.company = company.id order by id limit 1) as bld on (company.id = bld.company) > > > > > > -- с уважением, Таир Сабыргалиев ТОО "BEE Software" Республика Казахстан, 01 г.Астана, ул.Сарайшык 34, ВП-27 Тел.: +7 (7172) 56-89-31 Сот.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz Tair Sabirgaliev "BEE Software" Ltd. Republic of Kazakhstan, 01 Astana, Sarayshyk str. 34, sect. 27 Tel.: +7 (7172) 56-89-31 Mob.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
El 05/11/11 01:51, Steve Murphy escribió: I give! I'm flummoxed! Here is what I have, 3 tables: schedule companybuilding status0 3 x active 4 x active 5 x active 3 x active 3 x active 3 x active In the end, I want to replace the building id's above. They start out with the non-informative value of '1'; company id name status 3 x active 4 y active 5 z active building id company name 1 3 A active 2 3 B active 3 3 C active 4 4 D active 5 4 E active 6 4 F active 7 5 G active 8 5 H active 9 5 Iactive So, every company has 3 schedules. Of the 3, I'd like to select the *one* with the lowest id. I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair. select schedule.id as sched_id, bld.id as bid from schedule left join company on schedule.company = company.id left join (select * from building where building.company = company.id order by id limit 1) as bld where schedule.status = 'active' and company.status = 'active' and bld.status = 'active'; I get a syntax error on the the "where". ERROR: syntax error at or near "where" at character …. LINE 6: where ^ If I leave out the where clause entirely, that's an error also, ERROR: syntax error at or near ";" at character …. LINE 5: … as bld ; ^ semicolon> So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't going to tell me what. What am I missing? The join condition maybe? left join (select) as bid on ..something
Re: [GENERAL] Distinct on a non-sort column
On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis wrote: > I am trying to write a query that selects recent submissions (sorted by > submission_date) but only selects the most recent one for each user_id. > > example query: select distinct on (user_id) * from stories order by > date_submitted desc limit 10; > > However postgres will not allow me to filter out duplicate rows with > distinct unless I sort on that column, which would product useless results > for me. Group by seems to have similiar problems, plus the additional > problem of wanting aggregate functions to be used. > > I even tried sorting in a subquery, but it still comes out sorted by > user_id: select distinct on (user_id) * from stories where sid in (select > sid from stories order by date_submitted desc limit 10); > > > How can I work around this limitation to get the results sorted the way I > want, then have the duplicates removed? I can't be the only one running into > this limitation, there must be some workaround. > assuming date_submitted are unique for user_id: select * from stories s, (select user_id, max(date_submitted) d from stories group by user_id) ss where s.user_id = ss.user_id and s.date_submitted = ss.date_submitted; -- с уважением, Таир Сабыргалиев ТОО "BEE Software" Республика Казахстан, 01 г.Астана, ул.Сарайшык 34, ВП-27 Тел.: +7 (7172) 56-89-31 Сот.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz Tair Sabirgaliev "BEE Software" Ltd. Republic of Kazakhstan, 01 Astana, Sarayshyk str. 34, sect. 27 Tel.: +7 (7172) 56-89-31 Mob.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
On 05/11/2011 04:51, Steve Murphy wrote: > select schedule.id as sched_id, bld.id as bid > from > schedule > left join company on schedule.company = company.id > left join (select * from building where building.company = > company.id order by id limit 1) as bld > where > schedule.status = 'active' and company.status = 'active' and > bld.status = 'active'; You're missing the "on" bit after the join, and I think an alias for the inline view also: ...left join () x on (schedule.whatever = x.whatever) BTW it's a good idea to use explicit column names, not "select *" - makes for easier bug-finding. 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] explain analyse and nested loop joins
Hi, I have a query I'm trying to optimise. It takes just under a second to run, not too bad for my users but I'm worried that as the size of the data increases, it will get worse. Of course the plan may change when that happens but I'd also like to learn a bit more about optimisation anyway. The explain is here: http://explain.depesz.com/s/Ost - that one took 690ms. Seeing it had a couple of nested joins at the top, I 'set enable_nestloop = false;', resulting in an improvement of about 20x: http://explain.depesz.com/s/BRi The query is below. It joins to a second report dbvcalc_delivery_charges which I can also send if necessary. I've only guesses as to the reasons the default plan is slow or how to affect it, can someone enlighten me? Regards Oliver Kohll www.agilebase.co.uk -- 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: explain analyse and nested loop joins
Oops, forgot to include the query, it's SELECT b2deliveryorders.idb2deliveryorders, a2clientpremises.ida2clientpremises, a2clientpremises.premisesname, a2clientpremises.town, b2deliveryorders.expectedby, b2deliveryorders.dateordered, b2deliveryorders.invoicenumber, b2deliveryorders.deliverymethod, b2deliveryorders.driver, dbvcalc_delivery_charges.total, dbvcalc_delivery_charges.boxes, b2deliveryorders.createdbyauto FROM b2deliveryorders LEFT JOIN a2clientpremises ON b2deliveryorders.a2clientpremisespremisesname = a2clientpremises.ida2clientpremises LEFT JOIN dbvcalc_delivery_charges ON b2deliveryorders.idb2deliveryorders = dbvcalc_delivery_charges.idb2deliveryorders WHERE b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false ORDER BY b2deliveryorders.expectedby NULLS FIRST; Oliver Begin forwarded message: > From: Oliver Kohll - Mailing Lists > Subject: explain analyse and nested loop joins > Date: 5 November 2011 19:21:23 GMT > To: pgsql-general > > Hi, > > I have a query I'm trying to optimise.
Re: [GENERAL] Distinct on a non-sort column
Cstdenis writes: > I am trying to write a query that selects recent submissions (sorted by > submission_date) but only selects the most recent one for each user_id. > example query: /select distinct on (user_id) * from stories order by > date_submitted desc limit 10;/ > However postgres will not allow me to filter out duplicate rows with > distinct unless I sort on that column, which would product useless > results for me. Do the DISTINCT ON in a sub-query, with an ORDER BY appropriate for that task, and then re-sort the rows the way you want them presented in the outer query. SELECT ... FROM (SELECT DISTINCT ON ... ORDER BY ...) ss ORDER BY ...; 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] Distinct on a non-sort column
On 11/05/11 11:39 AM, Cstdenis wrote: example query: /select distinct on (user_id) * from stories order by date_submitted desc limit 10;/ select user_id,max(date_submitted) from stories group by date_submitted; ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
Steve, Op 05-11-11 05:51, Steve Murphy schreef: I give! I'm flummoxed! Here is what I have, 3 tables: schedule company building status0 3 x active 4 x active 5 x active 3 x active 3 x active 3 x active In the end, I want to replace the building id's above. They start out with the non-informative value of '1'; company id name status 3 x active 4 y active 5 z active building id company name 1 3 A active 2 3 B active 3 3 C active 4 4 D active 5 4 E active 6 4 F active 7 5 G active 8 5 H active 9 5 I active So, every company has 3 schedules. Of the 3, I'd like to select the *one* with the lowest id. I think you mean every company has three buildings... I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair. select schedule.id as sched_id, bld.id as bid from schedule left join company on schedule.company = company.id left join (select * from building where building.company = company.id order by id limit 1) as bld where schedule.status = 'active' and company.status = 'active' and bld.status = 'active'; I get a syntax error on the the "where". ERROR: syntax error at or near "where" at character …. LINE 6: where ^ If I leave out the where clause entirely, that's an error also, ERROR: syntax error at or near ";" at character …. LINE 5: … as bld ; ^ So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't going to tell me what. What am I missing? I think you actually want to do this: update schedule set building = (select id from building where company = schedule.company order by id limit 1); Best, Antonio. -- 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] Distinct on a non-sort column
On 11/5/2011 12:49 PM, Tom Lane wrote: Cstdenis writes: I am trying to write a query that selects recent submissions (sorted by submission_date) but only selects the most recent one for each user_id. example query: /select distinct on (user_id) * from stories order by date_submitted desc limit 10;/ However postgres will not allow me to filter out duplicate rows with distinct unless I sort on that column, which would product useless results for me. Do the DISTINCT ON in a sub-query, with an ORDER BY appropriate for that task, and then re-sort the rows the way you want them presented in the outer query. SELECT ... FROM (SELECT DISTINCT ON ... ORDER BY ...) ss ORDER BY ...; regards, tom lane If I understand that you are proposing as select * from (select distinct on (user_id) * from stories as s order by user_id) as foo order by date_submitted desc limit 10; I think it has the problem of which of the stories by that user is selected is random rather than the most recent being guaranteed (because the distinct is done before the sort). Or am I misunderstanding this? The suggestions by others of using max(date_submitted) may be a good workaround for this, but I also need to do the same thing sorted by a calculated score value which I do not think will be sufficiently unique for Tair's suggestion.
Re: [GENERAL] Distinct on a non-sort column
Cstdenis writes: > If I understand that you are proposing as > select * from > (select distinct on (user_id) * from stories as s order > by user_id) as foo > order by date_submitted desc limit 10; No, you always need to sort by *more* columns than are listed in DISTINCT ON. That's what determines which row is picked in each DISTINCT group. Read the SELECT reference page's example of how to use DISTINCT ON. 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] Some services of pgfoundry down?
It seems web and ssh service on pgfoundry are not available at this moment. Anyone knows why? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
