Re: [GENERAL] triggers and execute...

[Dimitri Fontaine]

On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
>   part text;
>   q text;
> BEGIN
>   part = to_char(new."timestamp",'MMDD');
>   q = 'insert into page_access_'||part||' values (new.*)';

What you want looks like this (thanks RhodiumToad):

 'INSERT INTO page_access_' || part ||
 'SELECT (' || quote_literal(textin(record_out(NEW))) || '::page_access).*;'

That's supposing you have a parent table named page_access, of course. And 
casting this way has drawbacks too (which I can't recall at this moment), but 
I've been using this live for maybe more than a year now without any problem.

> It works.  So, how am I supposed to run it with dynamic table names?

Hack your way around, partitioning is not yet there "for real"...
-- 
dim


signature.asc
Description: This is a digitally signed message part.

[GENERAL] How to begin to debug FATAL: invalid frontend message type 77 error messages?

[Keaton Adams]

Any ideas on how to debug these types of error messages?

Apr 30 01:36:02 mxlqa401 postgres[23600]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:02 mxlqa401 postgres[23601]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:02 mxlqa401 postgres[23602]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:13 mxlqa401 postgres[23631]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:13 mxlqa401 postgres[23632]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:13 mxlqa401 postgres[23633]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:24 mxlqa401 postgres[23664]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:24 mxlqa401 postgres[23666]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:24 mxlqa401 postgres[23665]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:35 mxlqa401 postgres[23696]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:35 mxlqa401 postgres[23698]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:35 mxlqa401 postgres[23697]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:46 mxlqa401 postgres[23728]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:46 mxlqa401 postgres[23730]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:46 mxlqa401 postgres[23729]: [3-1] LOG:  unexpected EOF on client 
connection



Thanks,

Keaton

[GENERAL] How to begin to debug FATAL: invalid frontend message type 77 error messages?

[Keaton Adams]

Any ideas on how to debug these types of error messages?

Apr 30 01:36:02 mxlqa401 postgres[23600]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:02 mxlqa401 postgres[23601]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:02 mxlqa401 postgres[23602]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:13 mxlqa401 postgres[23631]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:13 mxlqa401 postgres[23632]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:13 mxlqa401 postgres[23633]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:24 mxlqa401 postgres[23664]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:24 mxlqa401 postgres[23666]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:24 mxlqa401 postgres[23665]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:35 mxlqa401 postgres[23696]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:35 mxlqa401 postgres[23698]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:35 mxlqa401 postgres[23697]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:46 mxlqa401 postgres[23728]: [3-1] FATAL:  invalid frontend 
message type 77
Apr 30 01:36:46 mxlqa401 postgres[23730]: [3-1] LOG:  unexpected EOF on client 
connection
Apr 30 01:36:46 mxlqa401 postgres[23729]: [3-1] LOG:  unexpected EOF on client 
connection

bash-3.2$ psql -V
psql (PostgreSQL) 8.1.17
contains support for command-line editing

bash-3.2$ uname -a
Linux mxlqa401.corp.mxlogic.com 2.6.18-92.el5PAE #1 SMP Tue Apr 29 13:31:02 EDT 
2008 i686 athlon i386 GNU/Linux


Thanks,

Keaton

[GENERAL] possible consistency problem

[Sebastian Böhm]

Hi,

some days ago I removed all foreign keys from the database to speed up  
an import.


Now I tried to create the keys again but it says:

ALTER TABLE ONLY xxx ADD CONSTRAINT y FOREIGN KEY (zz)  
REFERENCES aaa(bb) ON UPDATE CASCADE;
ERROR:  constraint "yyy" for relation "" already  
exists, but it's not in the schema


when I do:
alter table only payments drop constraint y;

it says :
ERROR: "" is an index

note: the constraint in the error message in differed from the  
constraint in my drop statement !!! (I tried to drop the foreign key  
but in the error message it speaks of the primary key)


what is this ?


Thank you and Kind Regards
Sebastian


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PANIC: right sibling's left-link doesn't match

[Henry]

Greets,

I've recently completed a lengthy repartitioning exercise on half a  
dozen tables, restoring data, etc.


Something strange must have happened at some point (unrelated to the  
partitioning exercise - a crash related to file descriptors if I  
recall [not Pg's fault, mine]), because when I tried to modify a  
parent table (which is partitioned):


ALTER TABLE tab ADD COLUMN col INT DEFAULT 0;

I get:

PANIC:  right sibling's left-link doesn't match: block 35 links to 227  
instead of expected 223 in index "pg_depend_depender_index"

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

-

I also had some 'damage' in another partitioned table, which I  
resolved with REINDEX TABLE on all the partitions (it failed on INSERT  
with an error about block 0 read problem or something).


I then restarted Pg in single-user mode and reindexed all the system tables.

The aforementioned ALTER TABLE now completes without error.  My  
question is:  can I trust the state/stability of the DB now, or should  
I dump, re-initdb and restore from scratch?  Only problem is the DB is  
large and takes 24*n hours to restore, so this is a last resort.


Thanks
Henry


This message was sent using IMP, the Internet Messaging Program.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Maintaining connectivity/failing gracefully when my application is connected to a PostgreSQL server over Wi-fi on a PDT/PDA handset

[Peter Geoghegan]

Hello,

I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
the connection is established over wi-fi, and wi-fi connectivity is
often flaky, I feel that I have to "fail gracefully" to as great an
extent as possible. The following utility function,
VerifyDbConnection(), is called before every piece of database work:

void MainInterface::VerifyDbConnection()
{
   if (PQstatus(conn) != CONNECTION_OK)
   {
   for(;;)
   {
   PQreset(conn);
   if(PQstatus(conn) == CONNECTION_OK)
   return;

   QMessageBox msgbox_connection_lost;
   msgbox_connection_lost.setText("Connection to
the database is lost, and cannot be re-established. "
   "This may be due to the fact that
you're too far away from the Wi-fi access point, or because "
   "the backoffice computer is turned off,
or it may be a low level connectivity problem." );
   QPushButton* retry_button =
msgbox_connection_lost.addButton(tr("Reconnect"),
QMessageBox::ActionRole);
   QPushButton* exit_button =
msgbox_connection_lost.addButton(tr("Exit"), QMessageBox::ActionRole);
   msgbox_connection_lost.setWindowTitle("Connection lost");
   msgbox_connection_lost.exec();

   if(msgbox_connection_lost.clickedButton() ==
retry_button)
   {
   continue;
   }
   else if(msgbox_connection_lost.clickedButton()
== exit_button)
   {
   exit(0);
   return;
   }
   }
   }

}

This seemed to work fine initially; I'd abruptly stop the database
server, and I would see a messagebox informing me of a connectivity
problem. Then, I'd start the server, click the retry button, and have
connectivity restored. However, when I walk out of range of the wi-fi
access point, which is the probable cause of losing connectivity in
the real world, my program crashes without displaying an error message
(I would expect to see a visual C++ runtime error message).

Can someone suggest a reason for this, or a workaround?

Regards,
Peter Geoghegan

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] could not bind IPv4 socket

[Grzegorz Buś]

Hi,

 

I'm using Centos 4.7 and after upgrading PostgreSQL to version 8.3.7 (from
ver. 7.4)  via yum I cannot connect to my postgres server from remote hosts.


 

I know that for PostgreSQL servers starting with version 8.0 remote
connections can only be set in postgresql.conf allowing them in
"listen_addresses" parameter. My settings are following:

 

listen_addresses = 'localhost,XXX.XXX.XXX.XXX'

 

where XXX.XXX.XXX.XXX is IP address of remote client that I want to be able
to use PostgreSQL server. Port is default.

 

When I start server (service postgresql start) I receive status [OK], but in
pgstartup.log there is information about some errors (or rather warnings -
but that's why remote connections don't work):

 

WARNING:  could not create listen socket for "XXX.XXX.XXX.XXX"

LOG:  could not bind IPv4 socket: Cannot assign requested address

HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.

 

Of course - there is no other instance of PostgreSQL serve nor any other
application is using this TCP port. This commands returns empty string: 

 

netstat -plunt | grep 5432

pg -A | grep postmaster

 

I must add that everything works fine for my local apps - they can easily
connect to the server. I also tried few other non-default ports from range
6+ with same result. Additionally, previous version (7.4) had no
problems for accepting remote connections.

 

Thanks,

Grzegorz Bus

[GENERAL] Pgsql errors, DBI and CGI::Carp

[Toomas Vendelin]

I'm writing CGI scripts in Perl using Postgresql via DBI interface.  
RAISE_ERROR is on.


For some reason (unlike with MySQL), when a Perl script dies from  
Postgresql error, the line number of Perl script where the error  
occurred is not reported, just the SQL statement line number is given.  
In a longer script looking it may become a tedious task to guess the  
line in script that caused the problem. Is that an expected behavior  
or am I missing something?


Toomas

--
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] triggers and execute...

[Scott Marlowe]

On Tue, Apr 28, 2009 at 11:24 PM, Scott Marlowe  wrote:
> On Tue, Apr 28, 2009 at 10:46 PM, David Fetter  wrote:
>> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>>>  wrote:
>>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
>>> > wrote:
>>> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>> >>
>>> >> create or replace function page_access_insert_trigger ()
>>> >> returns trigger as $$
>>> >> DECLARE
>>> >>        part text;
>>> >>        q text;
>>> >> BEGIN
>>> >>        part = to_char(new."timestamp",'MMDD');
>>> >>        q = 'insert into page_access_'||part||' values (new.*)';
>>> >> ...
>>> >>
>>> >> When I create it and try to use it I get this error:
>>> >> ERROR:  NEW used in query that is not in a rule
>>> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>>> >
>>> > At this point I don't think that there is a way for this function to
>>> > know the correct table type of new.* since page_access_... is still
>>> > only a concatenated string.  There there a way to cast new.* to the
>>> > correct table type as part of this insert statement?
>>>
>>> Oh man, it just gets worse.  I really need a simple elegant solution
>>> here, because if I try to build the query by hand null inputs make
>>> life a nightmare.  I had built something like this:
>>>
>>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>>                 '||new.paid||',
>>>                 '''||new.timestamp||''',
>>>                 '||new.total_time||',
>>>                 '''||new.http_host||''',
>>>                 '''||new.php_self||''',
>>>                 '''||new.query_string||''',
>>>                 '''||new.remote_addr||''',
>>>                 '''||new.logged_in||''',
>>>                 '||new.uid||',
>>>                 '''||new.http_user_agent||''',
>>>                 '''||new.server_addr||''',
>>>                 '''||new.notes||'''
>>>         )';
>>>         execute q;
>>>
>>> But if any of the fields referenced are null, the whole query string
>>> is now null.  So the next step is to use coalesce to build a query
>>> string?  That get insane very quickly.  There's got to be some
>>> quoting trick or something to let me use new.*, please someone see
>>> this and know what that trick is.
>>
>> Well, you can add in piles of COALESCE, but that way madness lies.
>>
>> Instead, use dollar quoting, the appropriate quote_*() functions, and
>> this:
>>
>> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
>
> Thanks so much!  I'm off to read up on it.  Dollar quoting, quote()
> and the wiki.  Thanks again.

OK, I wrote a quick test and it's not working.  I've tried a few
combinations here and there but nothing seems to kick it off.

create or replace function page_access_test ()
returns trigger as $$
DECLARE
var text;
BEGIN
EXECUTE 'SELECT (' ||
 quote_literal(NEW) || '::' || TG_RELID::regclass ||
 ').' || quote_ident(http_host)
 INTO var;
raise notice '%',var;
END;
$$ language plpgsql;

which generates the error:

ERROR:  column "http_host" does not exist

I'm pretty sure that column exists in the table.  Here's the line for
\d on page_access:

 http_host   | text

I've tried new.http_host, which when http_host='xyz' generates an
ERROR:  type "public.xyz" does not exist

It's late, I'll mess with this tomorrow.  This is really frustrating
me and I feel dirty if I resort to a cron job to create the new table.
 I've tested the basic time to do all the work on my laptop and the
code runs pretty fast there.  So checking to see if the table is there
doesn't seem a particularly expensive select.  It's on a small system
table that stays cached.  My laptop can run the main code loop with
inserts (and lying fsync of course) 1500 times per second.  Without
the check it can run 1700 a second.  We do a dozen a minute.  So
unless our application goes insane and starts inserting data a couple
thousand times faster it's a non-issue.

I want a simple, self sustaining solution that requires no cron jobs
to work.  If someone has a simple dynamic trigger example in any
scripting language like plpgsql, plperl or pltcl please post it.  I
don't want to maintain C triggers for this on a production server.  If
I can't get it working I'll implement the cron job.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Connecting to a postgreSQL database with windows CE over wi-fi; failing gracefully

[Peter Geoghegan]

Hello,

I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
the connection is established over wi-fi, and wi-fi connectivity is
often flaky, I feel that I have to "fail gracefully" to as great an
extent as possible. The following utility function,
VerifyDbConnection(), is called before every piece of database work:

void MainInterface::VerifyDbConnection()
{
if (PQstatus(conn) != CONNECTION_OK)
{
for(;;)
{
PQreset(conn);
if(PQstatus(conn) == CONNECTION_OK)
return;

QMessageBox msgbox_connection_lost;
msgbox_connection_lost.setText("Connection to the 
database is lost,
and cannot be re-established. "
"This may be due to the fact that you're too 
far away from the
Wi-fi access point, or because "
"the backoffice computer is turned off, or it 
may be a low level
connectivity problem." );
QPushButton* retry_button =
msgbox_connection_lost.addButton(tr("Reconnect"),
QMessageBox::ActionRole);
QPushButton* exit_button =
msgbox_connection_lost.addButton(tr("Exit"), QMessageBox::ActionRole);
msgbox_connection_lost.setWindowTitle("Connection 
lost");
msgbox_connection_lost.exec();

if(msgbox_connection_lost.clickedButton() == 
retry_button)
{
continue;
}
else if(msgbox_connection_lost.clickedButton() == 
exit_button)
{
exit(0);
return;
}
}
}

}

This seemed to work fine initially; I'd abruptly stop the database
server, and I would see a messagebox informing me of a connectivity
problem. Then, I'd start the server, click the retry button, and have
connectivity restored. However, when I walk out of range of the wi-fi
access point, which is the probable cause of losing connectivity in
the real world, my program crashes without displaying an error message
(I would expect to see a visual C++ runtime error message).

Can someone suggest a reason for this, or a workaround?

Regards,
Peter Geoghegan

-- 
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] triggers and execute...

[David Fetter]

On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>  wrote:
> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe  
> > wrote:
> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
> >>
> >> create or replace function page_access_insert_trigger ()
> >> returns trigger as $$
> >> DECLARE
> >>        part text;
> >>        q text;
> >> BEGIN
> >>        part = to_char(new."timestamp",'MMDD');
> >>        q = 'insert into page_access_'||part||' values (new.*)';
> >> ...
> >>
> >> When I create it and try to use it I get this error:
> >> ERROR:  NEW used in query that is not in a rule
> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
> >
> > At this point I don't think that there is a way for this function to
> > know the correct table type of new.* since page_access_... is still
> > only a concatenated string.  There there a way to cast new.* to the
> > correct table type as part of this insert statement?
> 
> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:
> 
> q = 'insert into '||schem||'.page_access_'||part||' values (
> '||new.paid||',
> '''||new.timestamp||''',
> '||new.total_time||',
> '''||new.http_host||''',
> '''||new.php_self||''',
> '''||new.query_string||''',
> '''||new.remote_addr||''',
> '''||new.logged_in||''',
> '||new.uid||',
> '''||new.http_user_agent||''',
> '''||new.server_addr||''',
> '''||new.notes||'''
> )';
> execute q;
> 
> But if any of the fields referenced are null, the whole query string
> is now null.  So the next step is to use coalesce to build a query
> string?  That get insane very quickly.  There's got to be some
> quoting trick or something to let me use new.*, please someone see
> this and know what that trick is.

Well, you can add in piles of COALESCE, but that way madness lies.

Instead, use dollar quoting, the appropriate quote_*() functions, and
this:

http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump and pg_restore problem

[Alan Hodgson]

On Wednesday 29 April 2009, "Michele Petrazzo - Unipex" 
 wrote:
> The unique solution that I found it's that to export with pg_dump all my
> tables except the table_three and, after, exporting only that and on the
> other host and import that alone...
>
> It's this a normal behavior, a "missing feature", or...?

pg_dump and pg_restore correctly backup and restore complete databases. 
Partial backups and restores work, but of course can create expected 
dependency problems that only the operator is in a position to resolve. 

In your case you restored the complete schema and then tried to import data, 
which failed due to a foreign key constraint. In a full backup and restore, 
all the table data would have been restored prior to that constraint being 
created.

-- 
Even a sixth-grader can figure out that you can’t borrow money to pay off 
your debt

-- 
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: syntax error at or near "IF"... why?

[DaNieL..!]

On 30 Apr, 07:30, Johan Nel  wrote:
> Daniel,
>
> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
> >  ROLLBACK;
> > END IF
> > COMMIT;
>
> > i always get the error
> > ERROR:  syntax error at or near "IF"
>
> > Where am i mistaken?
>
> SELECT returns in essence a record or setof records.
>
> DECLARE _credit int;
> ...
> SELECT credit FROM users WHERE name = 'mary' INTO _credit;
> IF _credit < 0 THEN
>    ROLLBACK;
> END IF;
>
> If there is a chance that the select returns more than one record you
> can do something similar to:
> DECLARE rec record;
> ...
> FOR rec IN (SELECT credit FROM users WHERE name = 'mary'
> LOOP
>    IF rec.credit < 0 THEN
>      ...
>    ELSE
>      ...
>    END IF;
> END LOOP;
>
> HTH,
>
> Johan Nel
> Pretoria, South Africa.

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
---
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
   ^
---

For the if statement, i've tryed that
IF 2 = 2 THEN
 ROLLBACK;
END IF

but still the error near the "IF"..
dunno.. in mysql the if can be used in that way.. in postgres no?

-- 
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: syntax error at or near "IF"... why?

[Raymond O'Donnell]

On 30/04/2009 07:45, DaNieL..! wrote:

> I tryed the declare, before and after the BEGIN;, but allways returns
> me the error:
> ---
> ERROR: syntax error at or near "int";
> LINE 1: DECLARE _mycredit int;
>^
> ---

I missed what came before in this thread, but in plpgsql functions the
DECLARE comes before BEGIN:

  create or replace function my_function() returns
  as
  $$
  declare

  begin

return
  end;
  $$
  language plpgsql;

Can you show us the full function code again please?

Ray.


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] delete duplicates takes too long

[Dennis Brakhane]

On Sat, Apr 25, 2009 at 2:51 AM, Alvaro Herrera
 wrote:
> In that case you can create a partial unique index:
>
> create index foo on cdr_ama_stat (abonado_a, abonado_b, fecha_llamada,
> duracion) where processed = 2;

Of course, the unique is missing in this one:

CREATE UNIQUE INDEX foo ON cdr_ama_stat (abonado_a, abonado_b, fecha_llamada,
duracion) WHERE processed = 2;

-- 
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: syntax error at or near "IF"... why?

[Johan Nel]

DaNieL..! wrote:

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
---
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
   ^
---

For the if statement, i've tryed that
IF 2 = 2 THEN
 ROLLBACK;
END IF

but still the error near the "IF"..
dunno.. in mysql the if can be used in that way.. in postgres no?


Sorry was thinking FUNCTION way of doing it.  DECLARE can only be used 
in a stored procedure/function.


Johan

--
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] possible consistency problem

[Alan Hodgson]

On Thursday 30 April 2009, Sebastian Böhm  wrote:
> when I do:
> alter table only payments drop constraint y;
>
> it says :
> ERROR: "" is an index
>
> note: the constraint in the error message in differed from the
> constraint in my drop statement !!! (I tried to drop the foreign key
> but in the error message it speaks of the primary key)
>
> what is this ?

Was this server a slony slave, per chance?

-- 
Even a sixth-grader can figure out that you can’t borrow money to pay off 
your debt

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Time zone HADT timestamp syntax error in trigger

[John Smithus]

Greetings!

I'm encountering an "invalid input syntax for type timestamp" error in
a trigger.

The server is running PostgreSQL 8.3.7 on an AMD64 Gentoo Linux
machine. The system time zone is set to 'America/Adak' and datestyle
is set to 'sql, mdy' in postgresql.conf.

The target column type is "timestamp (0) with time zone", but for
testing, I am able to duplicate the results using the trigger example
located at the following location:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE


I create a new database, create the example table, function, and
trigger, and then perform the following query is psql:

INSERT INTO emp (empname, salary) VALUES ('John', '3000');


It immediately returns with:

ERROR:  invalid input syntax for type timestamp: "04/30/2009
08:48:05.760442 HADT"
CONTEXT:  PL/pgSQL function "emp_stamp" line 16 at assignment


As I mentioned, I'm actually targeting a column type of "timestamp (0)
with time zone", but I'd like to know what modification should be done
to that trigger example to allow the assignment to succeed.

Any advice is greatly appreciated.

Thank you very much!

-- 
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 begin to debug FATAL: invalid frontend message type 77 error messages?

[Merlin Moncure]

On Thu, Apr 30, 2009 at 12:16 PM, Keaton Adams  wrote:
> Any ideas on how to debug these types of error messages?
>
> Apr 30 01:36:02 mxlqa401 postgres[23600]: [3-1] FATAL:  invalid frontend
> message type 77
> Apr 30 01:36:02 mxlqa401 postgres[23601]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:02 mxlqa401 postgres[23602]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:13 mxlqa401 postgres[23631]: [3-1] FATAL:  invalid frontend
> message type 77
> Apr 30 01:36:13 mxlqa401 postgres[23632]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:13 mxlqa401 postgres[23633]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:24 mxlqa401 postgres[23664]: [3-1] FATAL:  invalid frontend
> message type 77
> Apr 30 01:36:24 mxlqa401 postgres[23666]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:24 mxlqa401 postgres[23665]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:35 mxlqa401 postgres[23696]: [3-1] FATAL:  invalid frontend
> message type 77
> Apr 30 01:36:35 mxlqa401 postgres[23698]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:35 mxlqa401 postgres[23697]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:46 mxlqa401 postgres[23728]: [3-1] FATAL:  invalid frontend
> message type 77
> Apr 30 01:36:46 mxlqa401 postgres[23730]: [3-1] LOG:  unexpected EOF on
> client connection
> Apr 30 01:36:46 mxlqa401 postgres[23729]: [3-1] LOG:  unexpected EOF on
> client connection

what are you connecting to the database with?  I bet not libpq.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

[Craig Ringer]

Hi

This must be a fairly common requirement, but either I don't know how to
ask Google about it or there's not as much out there as I would've expected.

I'm looking for a way to map the output from a monotonically increasing
sequence (not necessarily gapless - ie a normal Pg SEQUENCE) into a
fairly random different value in the availible space with a 1:1
input->output relationship. In other words, for the input "27" the
output will always be the same (say 32 bit) number, and no other input
will produce that output.

Note that I'm *NOT* looking for a PRNG that takes the previous output as
its input. That'd force me to use the same techniques as for a gapless
sequence in Pg, with all the associated horror with locking and
deadlocks, the performance issues, etc.

Does anyone here know of a good algorithm to do this that doesn't just
iterate `n' times through a PRNG with the same seed, but instead does a
true non-colliding space mapping?

If I find something good and there aren't any existing Pl/PgSQL
implementations I'll post one for others' use, since I'm pretty sure it
must come up a lot. You don't want your database to send out "invoice
#1" or "customer #1" after all.

(I'm also going to be looking for efficient ways to calculate effective
check digits for arbitrary numbers within a certain range, too, and will
post something for that, but that comes later).

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Understand this error

[paulo matadr]

Hi all,
my database entry in mode recovery,
analyzing my pg_log I seem this:

system logger process (PID 6517) was terminated by signal 9
background writer process (PID 6519) was terminated by signal 9
terminating any other active server processes

and OS in var/logs:

 kernel:  [] out_of_memory+0x53/0x267
 kernel:  [] __alloc_pages+0x229/0x2b2
 kernel:  [] read_swap_cache_async+0x45/0xd8
 kernel:  [] swapin_readahead+0x60/0xd3
 kernel:  [] __handle_mm_fault+0x952/0xdf2
 kernel:  [] sock_def_readable+0x34/0x5f
 kernel:  [] unix_dgram_sendmsg+0x43d/0x4cf
 kernel:  [] do_page_fault+0x4b8/0x81d
 kernel:  [] do_sock_write+0xc4/0xce
 kernel:  [] dequeue_task+0x18/0x37
 kernel:  [] thread_return+0x0/0xea
 kernel:  [] error_exit+0x0/0x84
 kernel:  [] do_syslog+0x173/0x3ae
 kernel:  [] do_syslog+0x141/0x3ae
 kernel:  [] autoremove_wake_function+0x0/0x2e
 kernel:  [] kmsg_read+0x3a/0x44
 kernel:  [] vfs_read+0xcb/0x171
 kernel:  [] sys_read+0x45/0x6e
 kernel:  [] tracesys+0xd1/0xdc

 kernel: Free swap  = 0kB
 kernel: Total swap = 2031608kB
 kernel: Free swap:0kB
kernel: 4390912 pages of RAM
 kernel: 280785 reserved pages
 kernel: 10222 pages shared
kernel: 4 pages swap cached
kernel: Out of memory: Killed process 6519 (postmaster).

How prenvent postgres use all memory of system?Why this happen?

Thanks for all
Paulo


  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

Re: [GENERAL] triggers and execute...

[Alvaro Herrera]

Scott Marlowe escribió:

> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:
> 
> q = 'insert into '||schem||'.page_access_'||part||' values (
> '||new.paid||',
> '''||new.timestamp||''',
> '||new.total_time||',
> '''||new.http_host||''',
> '''||new.php_self||''',
> '''||new.query_string||''',
> '''||new.remote_addr||''',
> '''||new.logged_in||''',
> '||new.uid||',
> '''||new.http_user_agent||''',
> '''||new.server_addr||''',
> '''||new.notes||'''
> )';
> execute q;
> 
> But if any of the fields referenced are null, the whole query string
> is now null.  So the next step is to use coalesce to build a query
> string?  That get insane very quickly.  There's got to be some quoting
> trick or something to let me use new.*, please someone see this and
> know what that trick is.

Agreed, it is ugly.  I don't think there's a better way to do it though.

One thing you could try is getting the column names and types from the
catalogs to build the insert statement.  That way you don't have to list
each column separately, and you don't need to fiddle with whether each
value needs quotes or not.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how do you get there from here?

[Michael P. Soulier]

Tom Lane wrote:
>> Foreign-key constraints:
>> "dashboard_tuginstance_node_id_fkey" FOREIGN KEY (node_id)
>> REFERENCES nodes(id) DEFERRABLE INITIALLY DEFERRED
> 
> This is what's creating the limitation.

So, I'm working around it by creating the new columns with the default
value that I want populated, and then resetting the default. This works,
but it doesn't feel like a best practice.

Can someone recommend a better way to say, consolidate 3 columns down to
2 and conditionally move contents around to the new columns, inside of a
transaction involving ALTER TABLE calls?

I'm just wondering if there is a better solution than what I've done.

Mike
-- 
Michael P. Soulier , 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It
takes a touch of genius - and a lot of courage to move in the opposite
direction." --Albert Einstein



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] triggers and execute...

[Scott Marlowe]

On Wed, Apr 29, 2009 at 4:23 AM, Alban Hertroys
 wrote:
> On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote:
>
>> Oh man, it just gets worse.  I really need a simple elegant solution
>> here, because if I try to build the query by hand null inputs make
>> life a nightmare.  I had built something like this:
>>
>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>               '||new.paid||',
>>               '''||new.timestamp||''',
>>               '||new.total_time||',
>>               '''||new.http_host||''',
>>               '''||new.php_self||''',
>>               '''||new.query_string||''',
>>               '''||new.remote_addr||''',
>>               '''||new.logged_in||''',
>>               '||new.uid||',
>>               '''||new.http_user_agent||''',
>>               '''||new.server_addr||''',
>>               '''||new.notes||'''
>>       )';
>>       execute q;
>>
>> But if any of the fields referenced are null, the whole query string
>> is now null.  So the next step is to use coalesce to build a query
>> string?  That get insane very quickly.  There's got to be some quoting
>> trick or something to let me use new.*, please someone see this and
>> know what that trick is.
>
>
> I think you could do this if you'd be using a PL-language that supported
> reflection (on the NEW objects' type in this case). I can't say I know which
> one does though, I've only been using PL/pgsql so far, but I'd guess
> PL/Python, PL/Perl or PL/Java should be able to do the trick. Or plain C.
>
> AFAIK there's no way to dynamically list column names from a table-type
> variable like NEW in PL/pgsql, which is why the above probably can't be done
> any easier using PL/pgsql. It would be nice to be able to LOOP over a
> variable like that or some similar method (I guess a more relational
> approach where the columns would be available as a result set would be
> preferred), especially if it'd be similarly easy to inspect the name and
> type of each column.

I'm really close to using coalesce to make this work, since I can't
get the referenced at
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers to work.  If
there was some simple quoting trick to get the original (NEW.*) stuff
to work I'd be gold.

Either that or just implement this all in rules with a simple cron job
that creates the new table as needed a week or so in advnace.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Issue with SSAS

[Peeyush Jain]

Hi All,
This is regarding the issue facing with SSAS (Sql Server Analysis Services).


Issue:
While creating a SSAS application I am not able to create data source view with 
Npgsql. I can able to create data source successfully.

While creating data source view Npgsql should show the list of tables in 
database but it not showing. Therefore, I can't go ahead.

Note: I have gone through sql native OLEDB provider and its works fine but only 
in case of Npgsql it seems issue with Npgsql side.

Any help or suggestion will be highly appreciated.


Thanks in advance.
Peeyush Jain


Peeyush Jain| Software Engineer -Netezza Dev | Persistent Systems Ltd
peeyush_j...@persistent.co.in  | Cell: 
+91 9373069475 | Tel: +91 (20) 3023 6762
Innovation in software product design, development and delivery- 
www.persistentsys.com




DISCLAIMER
==
This e-mail may contain privileged and confidential information which is the 
property of Persistent Systems Ltd. It is intended only for the use of the 
individual or entity to which it is addressed. If you are not the intended 
recipient, you are not authorized to read, retain, copy, print, distribute or 
use this message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent Systems 
Ltd. does not accept any liability for virus infected mails.

Re: [GENERAL] triggers and execute...

[Richard Broersma]

I wonder if it would be easier to perodically replace the entire
trigger function with one that inserts to the correct table using
CRON+SED rather than dynamically building SQL.  This might be a bad
idea however.  I'm just thinking outside the box.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] retrieving primary key for row with MIN function

[Marcin Krol]

Hello everyone,

I need to retrieve PK (r.id in the query) for row with 
MIN(r.start_date), but with a twist: I need to select only one record, 
the one with minimum date.


Doing it like this does not solve the problem:

SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, 
r.id AS reservation_id

FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND 
(r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)

GROUP BY h.id, r.id
ORDER BY reservation_start_date ASC

I have to use either GROUP BY r.id or use MIN(r.id). MIN(r.id) doesn't 
select the id from the row with corresponding MIN(r.start_date), so it's 
useless, while GROUP BY r.id produces more than one row:


host_id reservation_start_date  reservation_id
361 2009-05-11  38
361 2009-05-17  21

I need to select only row with reservation_id = 38.

I would rather not do subquery for every 'host' record, since there can 
be a lot of them...


Regards,
mk



--
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] retrieving primary key for row with MIN function

[Scott Marlowe]

On Wed, Apr 29, 2009 at 6:30 AM, Marcin Krol  wrote:
> Hello everyone,
>
> I need to retrieve PK (r.id in the query) for row with MIN(r.start_date),
> but with a twist: I need to select only one record, the one with minimum
> date.
>
> Doing it like this does not solve the problem:
>
> SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
> reservation_id
> FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
> r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
> GROUP BY h.id, r.id
> ORDER BY reservation_start_date ASC
>

Couldn't you just use something like

select r.id from (join tables here) order by date asc limit 1

or something like that?

-- 
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] could not bind IPv4 socket

[Greg Smith]

On Thu, 30 Apr 2009, Grzegorz Bu? wrote:


listen_addresses = 'localhost,XXX.XXX.XXX.XXX'
where XXX.XXX.XXX.XXX is IP address of remote client that I want to be able to 
use PostgreSQL server. Port is default.


listen_addresses gets set to the IP address of the server itself, the IP 
address it is "listening" for input on.  Since you're giving it a remote 
address, that's why it can't create a socket to listen there.


There is a second file here, pg_hba.conf, that filters down who can 
connect to the database.  Normal practice here is to set:


listen_address='*'

So that the server is remotely accessible from all of its interfaces, and 
then you can do all filtering of who can connect just via pg_hba.conf 
instead.  See 
http://www.postgresql.org/docs/8.3/static/auth-pg-hba-conf.html for more 
information.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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 begin to debug FATAL: invalid frontend message type 77 error messages?

[Craig Ringer]

Keaton Adams wrote:
> Any ideas on how to debug these types of error messages?

Maybe you should start by telling us:

- your PostgreSQL server version
- What platform your server is running on
- Your PostgreSQL client driver type and version ( native libpq? pgodbc?
jdbc? npgsql? etc)
- How you're using the client or client driver

... etc

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 08P01: unexpected EOF on client connection

[Tomas Vondra]

Hi,

I've run into some strange troubles with a quite simple web application 
that stores data in a PostgreSQL database. It is a simple image gallery, 
displaying multiple thumbnails (stored in a bytea column) on a single 
page generated by PHP. The problem is some of the images are not displayed.


I've found that the postgresql.log contains these messages (many of them):

...
LOG:  08006: could not receive data from client: Connection reset by peer
LOCATION:  pq_recvbuf, pqcomm.c:738
LOG:  08P01: unexpected EOF on client connection
LOCATION:  SocketBackend, postgres.c:307
LOG:  08P01: unexpected EOF on client connection
LOCATION:  SocketBackend, postgres.c:307
LOG:  08006: could not receive data from client: Connection reset by peer
LOCATION:  pq_recvbuf, pqcomm.c:738
LOG:  08006: could not receive data from client: Connection reset by peer
LOCATION:  pq_recvbuf, pqcomm.c:738
...

Due to this, there are no data written to the output, so the browser 
receives only "HTTP/1.x 200 OK" but does not receive the image itself 
(and thus cannot display it).


The script fetching the data from a database is something this:

$conn = pg_connect(...);
$res = pg_query("SELECT mime, thumbnail_data FROM images WHERE filename 
= ");

$row = pg_fetch_assoc($row);
header('Content-Type: ' . $row['mime']);
echo pg_unescape_bytea($row['thumbnail_data']);

And it fails for some reason with the log messages listed above.

This behavior does not depend on a browser (I've tried that on multiple 
ones), PHP version or Apache2 version (I've several more version). I've 
noticed this on PostgreSQL 8.2.12 and 8.3.6 (on Linux x86).


It obviously does not depend on a number of images, as my testing 
database contains only a few tiny images, and the "empty" images are 
different each time (so there are no corrupted data or something like that).


Do you have any idea why this happens?

regards
Tomas

--
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] Understand this error

[Scott Marlowe]

On Thu, Apr 30, 2009 at 7:00 AM, paulo matadr  wrote:
> Hi all,
> my database entry in mode recovery,
> analyzing my pg_log I seem this:
> system logger process (PID 6517) was terminated by signal 9
> background writer process (PID 6519) was terminated by signal 9
> terminating any other active server processes

Yeah, you're getting bitten by the OOM killer.  What changes, if any,
have you made to the postgresql.conf file?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

[Craig Ringer]

[reposting as the original seemed to get lost in a mail system issue on
my end. Sorry if it did make it.]

Hi

This must be a fairly common requirement, but either I don't know how to
ask Google about it or there's not as much out there as I would've
expected. At a high level, what I need is a way to "scramble" sequences
so it's not obvious that, say, customer 1332 is in fact the
one-thousand-and-thirty-second customer of the company, because the next
customer signed up might well get customer number 12, or 99442312.

I'm hoping to do this with a mapping of values from a normal sequence,
so that I can preserve the concurrency-friendly properties of normal
sequences and not have to deal with the nightmare of
gapless-sequence-like code.

So: I'm looking for a way to map the output from a monotonically
increasing sequence (not necessarily gapless - ie a normal Pg SEQUENCE)
within a particular range into a random-looking different value in the
same range with a 1:1 input->output relationship. In other words, for
the 32 bit integer input of (say) "27" the output will always be the
same (say 32 bit) number, and no other input will produce that output. A
reverse mapping is not necessary; I don't care about being able to find
out what input produced the output (say) 41231.

Note that I'm *NOT* looking for a PRNG that takes the previous output as
its input. That'd force me to use the same techniques as for a gapless
sequence in Pg, with all the associated horror with locking and
deadlocks, the performance issues, etc. That, or use a C extension
module (which I'd rather avoid for portability and future proofing) to
provide sequence-like properties.

Does anyone here know of a good algorithm to do this that doesn't just
iterate `n' times through a PRNG with the same seed, but instead does a
true non-colliding space mapping?

If I find something good and there aren't any existing Pl/PgSQL
implementations I'll post one for others' use, since I'm pretty sure it
must come up a lot. You don't want your database to send out "invoice
#1" or "customer #1" after all.

(I'm also going to be looking for efficient ways to calculate effective
check digits for arbitrary numbers within a certain range, too, and will
post something for that, but that comes later).

--
Craig Ringer


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] retrieving primary key for row with MIN function

[Richard Broersma]

On Wed, Apr 29, 2009 at 5:30 AM, Marcin Krol  wrote:

> I need to retrieve PK (r.id in the query) for row with MIN(r.start_date),
> but with a twist: I need to select only one record, the one with minimum
> date.

If you mean on row period then just add a limit 1 to the end of your
existing query.

> SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
> reservation_id
> FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
> r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
> GROUP BY h.id, r.id
> ORDER BY reservation_start_date ASC
LIMIT 1;


  If you mean the minimum start_date for each group of r.id then try distinct on

> SELECT DISTINCT ON ( r.id ) h.id AS host_id, r.start_date, r.id AS 
> reservation_id
> FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
> r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
> ORDER BY r.id, r.start_date;


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

[Steve Atkins]

On Apr 30, 2009, at 1:54 AM, Craig Ringer wrote:


Hi

This must be a fairly common requirement, but either I don't know  
how to
ask Google about it or there's not as much out there as I would've  
expected.


I'm looking for a way to map the output from a monotonically  
increasing

sequence (not necessarily gapless - ie a normal Pg SEQUENCE) into a
fairly random different value in the availible space with a 1:1
input->output relationship. In other words, for the input "27" the
output will always be the same (say 32 bit) number, and no other input
will produce that output.

Note that I'm *NOT* looking for a PRNG that takes the previous  
output as

its input. That'd force me to use the same techniques as for a gapless
sequence in Pg, with all the associated horror with locking and
deadlocks, the performance issues, etc.

Does anyone here know of a good algorithm to do this that doesn't just
iterate `n' times through a PRNG with the same seed, but instead  
does a

true non-colliding space mapping?

If I find something good and there aren't any existing Pl/PgSQL
implementations I'll post one for others' use, since I'm pretty sure  
it

must come up a lot. You don't want your database to send out "invoice
#1" or "customer #1" after all.

(I'm also going to be looking for efficient ways to calculate  
effective
check digits for arbitrary numbers within a certain range, too, and  
will

post something for that, but that comes later).


XOR it with a constant. Or depending on your needs, just add a constant.
Or shuffle bits. Or some combination of those.

Cheers,
  Steve


--
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: syntax error at or near "IF"... why?

[Jaime Casanova]

On Thu, Apr 30, 2009 at 1:45 AM, DaNieL..!  wrote:
>>
>> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
>> >  ROLLBACK;
>> > END IF
>> > COMMIT;
>>
>> > i always get the error
>> > ERROR:  syntax error at or near "IF"
>>

if you're inside a server-side function then you cannot use COMMIT nor
ROLLBACK; if you aren't inside a server-side function then you cannot
use IF

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general