Re: [GENERAL] vacuum confusion

[Richard Huxton]

John Sidney-Woollett wrote:
I've got a 7.4.6 db running on linux where we've been vacuuming "full" 
the database each night, and have recently switched to "vacuum analyse" 
instead.

We keep seeing this message at the end of the vacuum run:
WARNING:  some databases have not been vacuumed in 2013308218 transactions
HINT:  Better vacuum them within 134175429 transactions, or you may have 
a wraparound failure.
VACUUM

Why are we seeing this message when the only databases in this cluster 
is the one we are vacuuming (each night), and template0 and template1?

Is there something that we're not doing right?
Are you doing template0/1 too? The transaction IDs are shared between 
all databases AFAIK. Before the numbers wrap-around any "low" numbers 
need to be replaced by a "frozen" marker (I think it's 0 or 1).

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters

[Postgre . News . Firma]

>>Von: Duffner
> Von: Shachar Shemesh
> Betreff: Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters

> >How do I call a StoredProcdure, written in PlPqSql
> >which returns a set of records (or tableWhatever)
> >and wants parameters,
> >from ADO while using "adCmdStoredProc" ?

> Which driver are you using? If it's the OLE DB provider, then
> there is a solution, 
> provided that you modify your stored procedure a little. 
> You can probably write a wrapper procedure, 
> if that's not acceptable as it is.

I had been told not to use the OLE driver because it is not working
correctly. 
Now I will give it a try anyway. 
There is nothing to change, because we are still investigating postgre to
know which way we will use it. 
And if :-)

[One day later]
I tried it and we will not use the OLE driver for a LONG time.
 
> If your function returns "refcursor", then OLE DB will
> interpret that to  mean that the function has rowset returns, and will
emulate 
> the standard calls surrounding returning multiple results.

When I tried to retrieve multiple cursors via ADO/ODBC I got a table
with the names of the cursors.
Is it possible to use these cursors somehow ?

Cu,
Andreas


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] vacuum confusion

[John Sidney-Woollett]

Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I 
assumed that they'd be in our database.

Do I need to need to (plain) vacuum, or vacuum full these template0 and 
template1? And is this something that can be done once a week rather 
than every night (like our main database)?

Thanks for your help.
John Sidney-Woollett
Richard Huxton wrote:
John Sidney-Woollett wrote:
I've got a 7.4.6 db running on linux where we've been vacuuming "full" 
the database each night, and have recently switched to "vacuum 
analyse" instead.

We keep seeing this message at the end of the vacuum run:
WARNING:  some databases have not been vacuumed in 2013308218 
transactions
HINT:  Better vacuum them within 134175429 transactions, or you may 
have a wraparound failure.
VACUUM

Why are we seeing this message when the only databases in this cluster 
is the one we are vacuuming (each night), and template0 and template1?

Is there something that we're not doing right?

Are you doing template0/1 too? The transaction IDs are shared between 
all databases AFAIK. Before the numbers wrap-around any "low" numbers 
need to be replaced by a "frozen" marker (I think it's 0 or 1).

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] vacuum confusion

[Richard Huxton]

John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I 
assumed that they'd be in our database.

Do I need to need to (plain) vacuum, or vacuum full these template0 and 
template1? And is this something that can be done once a week rather 
than every night (like our main database)?
Ah! Found the section of the manuals - see "Routine Database Maintenance 
Tasks" for details.

AFAIK it's a simple vacuum and once a week is more than enough. The 
manual recommends once every 500million transactions, though you can 
leave it longer.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] PostgreSQL Features

[Bruno Almeida do Lago]

Hi,

 

I’d like to know if there is something like “insert
/*+ append */ into” and materialized views (from Oracle) on PostgreSQL. 

 

How can I pass hints to postgre and what hints are available?

 

 

Regards,

Bruno

Re: [GENERAL] vacuum confusion

[John Sidney-Woollett]

Thanks Richard, I found the page too...
However the implication (and my confusion) is that you need to vacuum 
your own databases only. It's not clear (to me) that you have to do the 
same for template0 and template1 as well.

Perhaps when someone is updating the docs, something more explicit than this
 Recommended practice for most sites is to schedule a database-wide 
VACUUM once a day at a low-usage time of day, supplemented by more 
frequent vacuuming of heavily-updated tables if necessary. (If you have 
multiple databases in a cluster, don't forget to vacuum each one; the 
program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for 
routine vacuuming for space recovery.

could be replaced by this
 Recommended practice for most sites is to schedule a database-wide 
VACUUM once a day at a low-usage time of day, supplemented by more 
frequent vacuuming of heavily-updated tables if necessary. (If you have 
multiple databases in a cluster, don't forget to vacuum each one 
(INCLUDING template0 and template1); the program vacuumdb may be 
helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for 
space recovery.

And perhaps an explicit reference to vacuuming template0/1 in the 
section on "Preventing transaction ID wraparound failures" would be helpful.

I'll add a weekly cron job to vacuum these two template databases.
Thanks for your help again.
John Sidney-Woollett
Richard Huxton wrote:
John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I 
assumed that they'd be in our database.

Do I need to need to (plain) vacuum, or vacuum full these template0 
and template1? And is this something that can be done once a week 
rather than every night (like our main database)?

Ah! Found the section of the manuals - see "Routine Database Maintenance 
Tasks" for details.

AFAIK it's a simple vacuum and once a week is more than enough. The 
manual recommends once every 500million transactions, though you can 
leave it longer.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] WARNING: could not remove database directory

[Hubert Fröhlich]

Hi list,
I am working with Postgres 8.0.0 final on a SuSE Linux 9.2 box. I tried
dropdb ax20050206
WARNING:  could not remove database directory 
"/export/home/postgres/data2/base/115101837"
DROP DATABASE

(maybe there was still some vacuum activity on the database...)
a) There was a posting in 
http://search.postgresql.org/archives.search?cs=utf-8&fm=on&st=20&dt=back&q=%22could+not+remove+database+directory%22&ul=http%3A%2F%2Farchives.postgresql.org%2Fpgsql-general%2F%25&dp=0&o=0&ps=10&s=date
I have the same phenomenon, however with the final release, and with 
linux. Is the bug still there?
b) Can I safely remove the directory by hand?

Greetings,
Hubert
--
---
Dr.-Ing. Hubert Fröhlich
Bezirksfinanzdirektion München  
Alexandrastr. 3, D-80538 München, GERMANY
Tel. :+49 (0)89 / 2190 - 2980
Fax  :+49 (0)89 / 2190 - 2997
hubert dot froehlich at bvv dot bayern dot de
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] vacuum confusion

[John Sidney-Woollett]

I've just noticed that the v8 docs are MUCH better in explaining this 
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).

Perhaps if the same texts

play=# VACUUM;
WARNING:  some databases have not been vacuumed in 1613770184 transactions
HINT:  Better vacuum them within 533713463 transactions, or you may have 
a wraparound failure.
VACUUM


and warning

To be sure of safety against transaction wraparound, it is necessary to 
vacuum every table, including system catalogs, in every database at 
least once every billion transactions. We have seen data loss situations 
caused by people deciding that they only needed to vacuum their active 
user tables, rather than issuing database-wide vacuum commands. That 
will appear to work fine ... for a while.


were added to the 7.4.x docs that would help others too.
Thanks
John Sidney-Woollett
Richard Huxton wrote:
John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I 
assumed that they'd be in our database.

Do I need to need to (plain) vacuum, or vacuum full these template0 
and template1? And is this something that can be done once a week 
rather than every night (like our main database)?

Ah! Found the section of the manuals - see "Routine Database Maintenance 
Tasks" for details.

AFAIK it's a simple vacuum and once a week is more than enough. The 
manual recommends once every 500million transactions, though you can 
leave it longer.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] SQL query

[David Goodenough]

I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.

I have an address table, with all the normal fields and a customer name
field and an address type.  There is a constraint that means that the
combination of customer and type have to be unique.  Normally the
only record per customer will be of type 'default', but if for instance
the customer wants a different billing address I would add in a second
type='billing' address record.  

I then want to join this table to another table, say an invoice table,
and I want to use the billing address if present, otherwise the default
address.  I do not want to create either two addresses or to put both
addresses on the invoice.

I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to 
me.  

I thought of using an inner select for the join, and using limit 1 to 
get just the one, and forcing the order by to give me the billing
address by preference, but I am then dependant on the sort order
of the particular type values I am selecting from.

Is there a better way?  I am sure this kind of problem must have
been solved before.  

Thanks in advance for any help you can give

David

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] vacuum confusion

[John Sidney-Woollett]

(Apologies if this message comes twice - my imap server and mail client 
had a little problem)...

I've just noticed that the v8 docs are MUCH better in explaining this 
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).

Perhaps if the same texts

play=# VACUUM;
WARNING:  some databases have not been vacuumed in 1613770184 transactions
HINT:  Better vacuum them within 533713463 transactions, or you may have 
a wraparound failure.
VACUUM


and warning

To be sure of safety against transaction wraparound, it is necessary to 
vacuum every table, including system catalogs, in every database at 
least once every billion transactions. We have seen data loss situations 
caused by people deciding that they only needed to vacuum their active 
user tables, rather than issuing database-wide vacuum commands. That 
will appear to work fine ... for a while.


were added to the 7.4.x docs that would help others too.
Thanks
John Sidney-Woollett
John Sidney-Woollett wrote:
Thanks Richard, I found the page too...
However the implication (and my confusion) is that you need to vacuum 
your own databases only. It's not clear (to me) that you have to do the 
same for template0 and template1 as well.

Perhaps when someone is updating the docs, something more explicit than 
this

 Recommended practice for most sites is to schedule a database-wide 
VACUUM once a day at a low-usage time of day, supplemented by more 
frequent vacuuming of heavily-updated tables if necessary. (If you have 
multiple databases in a cluster, don't forget to vacuum each one; the 
program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for 
routine vacuuming for space recovery.

could be replaced by this
 Recommended practice for most sites is to schedule a database-wide 
VACUUM once a day at a low-usage time of day, supplemented by more 
frequent vacuuming of heavily-updated tables if necessary. (If you have 
multiple databases in a cluster, don't forget to vacuum each one 
(INCLUDING template0 and template1); the program vacuumdb may be 
helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for 
space recovery.

And perhaps an explicit reference to vacuuming template0/1 in the 
section on "Preventing transaction ID wraparound failures" would be 
helpful.

I'll add a weekly cron job to vacuum these two template databases.
Thanks for your help again.
John Sidney-Woollett
Richard Huxton wrote:
John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I 
assumed that they'd be in our database.

Do I need to need to (plain) vacuum, or vacuum full these template0 
and template1? And is this something that can be done once a week 
rather than every night (like our main database)?

Ah! Found the section of the manuals - see "Routine Database 
Maintenance Tasks" for details.

AFAIK it's a simple vacuum and once a week is more than enough. The 
manual recommends once every 500million transactions, though you can 
leave it longer.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL query

[Matt K]

David Goodenough wrote:
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to 
me.  
 

Use NULL to indicate that the customer type is default. Then you can 
query with:

select * from addresses where customer = ? 
and coalesce(type, 'billing') = 'billing'

If type is NULL, the comparison will be 'billing' = 'billing' - always true. If 
there's a bunch of non-null type addresses, you'll get the 'billing' one.
http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#AEN12003
Matt

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] SQL query

[Janning Vygen]

Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
>
> I have an address table, with all the normal fields and a customer name
> field and an address type.  There is a constraint that means that the
> combination of customer and type have to be unique.  Normally the
> only record per customer will be of type 'default', but if for instance
> the customer wants a different billing address I would add in a second
> type='billing' address record.
>
> I then want to join this table to another table, say an invoice table,
> and I want to use the billing address if present, otherwise the default
> address.  I do not want to create either two addresses or to put both
> addresses on the invoice.
>
> I could do this by doing a select * from addresses where customer = ?
> and type = 'billing', looking to see if there is a result row and if not
> repeating the query with type = 'default', but that seems inelegant to
> me.
>
> I thought of using an inner select for the join, and using limit 1 to
> get just the one, and forcing the order by to give me the billing
> address by preference, but I am then dependant on the sort order
> of the particular type values I am selecting from.

don't think "vertical" (adresses in rows), think "horizontal" (adresses in 
columns), like this:

SELECT 
  c.*,
  COALESCE(a1.street, a2.street) AS street,
  COALESCE(a1.zip, a2.zip) AS zip,
  COALESCE(a1.town, a2.town) AS town
FROM
  customer AS c
  LEFT JOIN adresses AS a1 USING (customer_id) 
  LEFT JOIN adresses AS a2 USING (customer_id) 
WHERE 
  a1.type = default
  AND a2.type = 'billing'

i just type the and did not tested it. the trick is to join adresses multiple 
times and get the right data with COALESCE function which returns the first 
value which is NOT NULL.

If you still have difficulties, please send your schema.

kind regards,
janning

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] SQL query

[Richard Huxton]

David Goodenough wrote:
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a customer name
field and an address type.  There is a constraint that means that the
combination of customer and type have to be unique.  Normally the
only record per customer will be of type 'default', but if for instance
the customer wants a different billing address I would add in a second
type='billing' address record.  

I then want to join this table to another table, say an invoice table,
and I want to use the billing address if present, otherwise the default
address.  I do not want to create either two addresses or to put both
addresses on the invoice.
Not sure whether a schema change is possible for you, but you might want 
 to have two tables -
  addresses (customer_id*, addr_id*, ...)
  addr_usage (customer_id*, addr_type*, addr_id)
Add a custom trigger that ensures for every customer_id there is a valid 
row in addr_usage for each addr_type (sales, billing, shipping etc).

That way you can have any mix of addresses you like, and it's explicit 
which address is for which purpose.
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] a few doubts regarding postgres

[Surabhi Ahuja]

 does postgres support notifications.
 
for eg. through one connection to the server, i 
begin a transaction and i do inserts there.
in another sessions, i want to get notified that 
inserts are taking place in someother session.
please help.
 
a few other questions
 

Q1. is there anyway by which we can give priorities 
to the transactions?
 
Q2. is there s concept of connection pool in 
postgres?
 
Q3. does postgres support multi threading? if yes 
how?
 
regards
surabhi

Re: [GENERAL] a few doubts regarding postgres

[Shridhar Daithankar]

On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
>  does postgres support notifications.
>
> for eg. through one connection to the server, i begin a transaction and i
> do inserts there. in another sessions, i want to get notified that inserts
> are taking place in someother session. please help.

Umm.. out of box, I don't think so. May be some triggers etc. can be placed 
for information..Can you describe what you are trying to achieve?

> a few other questions
>
> Q1. is there anyway by which we can give priorities to the transactions?

You need priorities to connections or transactions? 

> Q2. is there s concept of connection pool in postgres?

Yes. See pgpool(http://pgfoundry.org/projects/pgpool/)

> Q3. does postgres support multi threading? if yes how?

No. PostgreSQL backend process is single threaded. However each connection 
gets a separate backend process. Hence on SMP machines, all available CPUs 
can potentially be used for multiple connections.

Regards,
 Shridhar

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] a few doubts regarding postgres

[Richard Huxton]

Surabhi Ahuja wrote:
 does postgres support notifications. 
 
for eg. through one connection to the server, i begin a transaction and i do inserts there.
in another sessions, i want to get notified that inserts are taking place in someother session.
please help.
You might want to look in the manuals for NOTIFY and LISTEN.
a few other questions
 
Q1. is there anyway by which we can give priorities to the transactions?
No. It's not clear that the idea makes sense in general (though it might 
for specific examples). You can of course tune settings to favour 
inserts over selects, or favour large sorts etc.

Q2. is there s concept of connection pool in postgres?
Google for pgpool by Tatsuo Ishii
Q3. does postgres support multi threading? if yes how?
In the client or the server? Which client? From what point of view?
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] a few doubts regarding postgres

[John DeSoi]

On Feb 11, 2005, at 7:47 AM, Shridhar Daithankar wrote:
On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
 does postgres support notifications.
for eg. through one connection to the server, i begin a transaction 
and i
do inserts there. in another sessions, i want to get notified that 
inserts
are taking place in someother session. please help.
Umm.. out of box, I don't think so. May be some triggers etc. can be 
placed
for information..Can you describe what you are trying to achieve?
I think NOTIFY/LISTEN should do this just fine, but you won't get the 
notification unless the transaction where NOTIFY gets called is 
committed.

http://www.postgresql.org/docs/8.0/interactive/sql-notify.html
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] SQL query

[Bruno Wolff III]

On Fri, Feb 11, 2005 at 11:07:24 +,
  David Goodenough <[EMAIL PROTECTED]> wrote:
> 
> I thought of using an inner select for the join, and using limit 1 to 
> get just the one, and forcing the order by to give me the billing
> address by preference, but I am then dependant on the sort order
> of the particular type values I am selecting from.

You can order by boolean expressions such as type = 'billing'.
You can use that with LIMIT or DISTINCT ON to get just the address you want.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] views get 'lost'

[Christoph Pingel]

I'm still quite new to postgres, struggeling with 'views'... I'm on 
OS X, using aquadatastudio to interface the db.

Now, after issuing a 'CREATE VIEW' command, I get back a '1 record 
affected' message, and I can use the view. However, the view doesn't 
appear in the tree view of aquadatastudio (even after reconnecting), 
and after restarting postgresql, it's lost. I used to think that 
'CREATE VIEW...' is all I have to do, am I wrong?

TIA,
Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] a few doubts regarding postgres

[Terry Lee Tucker]

On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
>
> No. PostgreSQL backend process is single threaded. However each connection
> gets a separate backend process. Hence on SMP machines, all available CPUs
> can potentially be used for multiple connections.
>
> Regards,
>  Shridhar

So then, can I count on the be_pid attribute returned from PQnotifies to be a 
unique value associated only with a given user? I think the answer is yes, 
but just making sure.

Thanks...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Understanding EXPLAIN ANALYZE output

[Martijn van Oosterhout]

On Thu, Feb 10, 2005 at 07:34:07PM -0500, Tom Lane wrote:
> Martijn van Oosterhout  writes:
> > I've been wondering about that. A while ago the change was made from
> > outputting a NOTICE with the EXPLAIN output to returning a resultset.
> > If you could agree on what columns to return it might not be so hard
> > for the EXPLAIN to return full tuples...
> 
> The major stumbling block to that is that a table is inherently
> unordered, so you'd have to devise a labeling scheme to allow the
> node-tree structure to be represented properly.  And without WITH or
> CONNECT BY, it'd be a bit of a PITA for an application to decipher the
> labeling scheme again ...

To be honest, I'm not sure this a real problem. You could simply label
the first columns a rownumber and a depth number. In extreme cases you
might want a nodeid and an parent nodeid.

But IMHO I don't think people are going to be doing analysis on the
results from SQL. Much easier to just load the result into a perl (or
some other PL) script and process it there. Maybe when WITH and CONNECT
BY show up the capability should be there, but for now just returning
the data should be fine.

At the moment people are talking about parsing strings to get the
output. That output has the same issues as what's being proposed here,
we're just saving the parsing step.

However, tuple based output would be quite unreadable for humans, how
can one specify which output to return. EXPLAIN ANALYZE WITH TUPLES
query?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgp5HpakZBafD.pgp
Description: PGP signature

[GENERAL] views don't get 'lost' :-)

[Christoph Pingel]

Please ignore my previous post, if you still can... :-) I simply and 
embarassingly forgot to commit the view to the db ooops.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Understanding EXPLAIN ANALYZE output

[Martijn van Oosterhout]

On Thu, Feb 10, 2005 at 10:30:26PM -0500, Tom Lane wrote:
> Well, the point is that there are potentially three types of statements
> involved:
> 
>   1. SELECTs
>   2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
>   3. Utility statements that can't return tuples (ALTER, etc)



> The $64 question is whether anyone *needs* to make these distinctions.
> If we arrange to return SPI_OK_SELECT for category 2, then callers won't
> be able to tell the difference between categories 1 and 2, but on the
> other hand a zero-row result set will still be properly classified as
> a SELECT-like operation.  If we leave the API as it stands today then a
> zero-row result set looks like a category 3, which arguably is a worse
> categorization.

It occurs to me that distinguishing between 2 and 3 would be useful,
since knowing if there were zero rows returned or nothing should be
returned is a useful distinction. But in the general case, cases 1 and
2 can be distinguished by examining the sent query.

I guess it turns out that "type-of-statement" and "returns-tuples" are
orthoganal concepts and nobody realised this at the time.

> I'm not sure that any of the statements in question actually can return
> zero-row result sets today, but I think it would be bad to make these
> decisions on the basis of assuming that the case will never arise.

What's the right response to "SHOW nonexistant_variable;" ? Currently
it's an error return, which seems reasonable.

I can imagine that there are situations where it would be useful to be
able to extract the output of VACUUM and ANALYZE as resultsets.
However, there's no way for a client to signal it wants a result set.
And just changing the output stops it being logged.

Not an easy choice.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpebECY0IRw1.pgp
Description: PGP signature

Re: [GENERAL] Understanding EXPLAIN ANALYZE output

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> Well, the point is that there are potentially three types of statements
> involved:
> 
>   1. SELECTs
>   2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
>   3. Utility statements that can't return tuples (ALTER, etc)

I'm not sure this matters, but of course there are other non-utility
statements like UPDATE and DELETE. Does SPI return SPI_OK_SELECT for those?

Because you can definitely do "EXPLAIN ANALYZE UPDATE ..." or "EXPLAIN ANALYZE
DELETE ...".

-- 
greg


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Python Driver

[James Thompson]

On Thursday 10 February 2005 08:56 pm, Alex Turner wrote:
> Just a small warning for those people using python with postgresql:
> pysgresql and psycopg are very different animals.  You cannot drop in
> one as a replacement for the other, even though both 'claim' to be DB
> API 2.0 compliant. 

None of the python postgresql drivers are drop in replacements for each other.  
Some drivers, such as popy return all data as strings, some have broken 
fetchmany support, each IIRC return different exceptions in different 
situations.  Pygresql has its own API that I believe predates DBAPI 2.0 and 
support for the standard API was added at a later date.

shameless plug.

One feature of gnuenterprize.org's common library is a datasource system that 
uses a single connections.conf file that contains  entries like

[devel]
comment = Development DB
provider = psycopg
host = rdbms.gnuenterprise.org
dbname = devel

to setup a connection to a database named "devel".  Providers can be any of 
the 4 postgresql drivers, oracle, db2, ingres, mysql,  ado, odbc, etc, etc.  
Most if not all the dbsig 2 drivers are supported.  Other formats such a dbf 
and csv are supported to varying degrees.  You can switch out databases by 
editing that one file.  Providers are not limited to just databases either, 
our application server is just another provider in our system.  Differences 
between providers are handled transparently to the developer.

Take Care,
James

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] a few doubts regarding postgres

[Richard Huxton]

Terry Lee Tucker wrote:
On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
No. PostgreSQL backend process is single threaded. However each connection
gets a separate backend process. Hence on SMP machines, all available CPUs
can potentially be used for multiple connections.
Regards,
Shridhar

So then, can I count on the be_pid attribute returned from PQnotifies to be a 
unique value associated only with a given user? I think the answer is yes, 
but just making sure.
Yes (provided you're not running connection pooling).
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters

[Jeff Eckermann]

<[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Hi,
>
> How do I call a StoredProcdure, written in PlPqSql
> which returns a set of records (or tableWhatever)
> and wants parameters,
> from ADO while using "adCmdStoredProc" ?

I don't believe that works (anyone who thinks I'm wrong, please correct me). 
You will need to use select syntax, e.g. "select * from 
setreturningfunction(param1, param2,);"  If you are using ODBC, you may 
have a problem with that syntax, though I believe that the driver code has 
been patched to deal with that, and a new release is on its way out (or you 
could compile the source).

>
> ERROR:  set-valued function called in context that cannot accept a set
>
>
> -- VERY LONG VERSION OF THIS QUESTION
> -
>
>
> When I use Microsoft SQL-Server I make a SP like
>
> CREATE PROCEDURE _test AS
> select * from evt_event
> return 42
> GO
>
> I call it just via name.
>
>
>
>
>
>
> When I use PostgreSql I make a SQL-SP like
>
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> select * from t1;
> $body$
> LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> I call it just via name too.
>
>
>
> When I use PostgreSql I make a PLPG-SQL-SP like
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> declare
>   rs record;
> begin
> for rs in select * from t1 loop
> return next rs;
> end loop;
> RAISE NOTICE '42';
> return;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> How do I call this one ? (using adCmdStoredProc)
>
>
> In the SQL-Editor I may call both pg-SPs via "select * from _test()"
>
>
> EMS PostgreSql Manager SQL-Editor:
> VariantePG1: select * from _test() : ok
> VariantePG2: select * from _test() : ok
>
> Ado:
> RECORDSET rs = adoRecordSet.open ,, adCmdStoredProc
>
> VarianteMS : ok
> VariantePG1: ok
> VariantePG2: ERROR:  set-valued function called in context that cannot
> accept a set
>
>
> Help,
> Andreas
>
>
>
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] how to capture query?

[Mark]

Hi,
I'm getting some errors in log file saying "invalid character at
position
#20..." I know that this is most likely that query is wrong.

Is it possible to capture all queries that get send or at least the
invalid queries?

I'm using postgresql 7.4.3 on Red Hat 9

Thanks,
Mark



__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] vacuum confusion

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> However the implication (and my confusion) is that you need to vacuum 
> your own databases only. It's not clear (to me) that you have to do the 
> same for template0 and template1 as well.

You have to vacuum template1, but not template0 because the latter is
marked not datallowconn.  Not sure if this is adequately explained
anywhere.  The next-to-last para in section 21.1.3 does mention the
datallowconn exception, but perhaps doesn't spell it out well enough.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] vacuum confusion

[John Sidney-Woollett]

Thanks Tom, I figured it out after I saw the error message when trying 
to vacuum template0.

I think the 7.4.x docs could do with a more explanation.
The 8.0 docs are better although a more explicit explanation stating 
that you MUST vacuum analyze template1 (periodically) would be useful.

Thanks
John Sidney-Woollett
Tom Lane wrote:
John Sidney-Woollett <[EMAIL PROTECTED]> writes:
However the implication (and my confusion) is that you need to vacuum 
your own databases only. It's not clear (to me) that you have to do the 
same for template0 and template1 as well.

You have to vacuum template1, but not template0 because the latter is
marked not datallowconn.  Not sure if this is adequately explained
anywhere.  The next-to-last para in section 21.1.3 does mention the
datallowconn exception, but perhaps doesn't spell it out well enough.
			regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] how to capture query?

[Yury Don]

M> Hi,
M> I'm getting some errors in log file saying "invalid character at
M> position
M> #20..." I know that this is most likely that query is wrong.

M> Is it possible to capture all queries that get send or at least the
M> invalid queries?

M> I'm using postgresql 7.4.3 on Red Hat 9

M> Thanks,
M> Mark

In postgresql.conf write
log_min_error_statement = error

-- 
Best regards,
 Yurymailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] how to capture query?

[Brad Nicholson]

To log all queries - in your postgresql.conf file, set the following:
log_statement = true
Mark wrote:
Hi,
I'm getting some errors in log file saying "invalid character at
position
#20..." I know that this is most likely that query is wrong.
Is it possible to capture all queries that get send or at least the
invalid queries?
I'm using postgresql 7.4.3 on Red Hat 9
Thanks,
Mark
		
__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
 


--
Brad Nicholson  416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp. 

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] SQL query

[David Goodenough]

On Friday 11 February 2005 11:31, Matt K wrote:
> David Goodenough wrote:
> >I could do this by doing a select * from addresses where customer = ?
> >and type = 'billing', looking to see if there is a result row and if not
> >repeating the query with type = 'default', but that seems inelegant to
> >me.
>
> Use NULL to indicate that the customer type is default. Then you can
> query with:
>
> select * from addresses where customer = ?
> and coalesce(type, 'billing') = 'billing'
>
> If type is NULL, the comparison will be 'billing' = 'billing' - always
> true. If there's a bunch of non-null type addresses, you'll get the
> 'billing' one.
>
> http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#A
>EN12003
>
> Matt
>
Well coalesce is not something I had come across, learn something every day.

But I can not use this as the type (with the customer) are the primary key and
therefore not null.  I could do something like:-

  coalesce( nullif( 'default', type), 'billing')

but I think that might be over egging it a bit.  I will hope this one reserve
and remember coalesce for the future.

Thanks,

David

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] SQL query

[David Goodenough]

On Friday 11 February 2005 11:46, Richard Huxton wrote:
> David Goodenough wrote:
> > I realise this is not strictly a Postgreslql question, but if the best
> > way to solve it involves using PG extensions, such as the PG procedural
> > languages I am only going to do this on PG and so I am happy to use them.
> >
> > I have an address table, with all the normal fields and a customer name
> > field and an address type.  There is a constraint that means that the
> > combination of customer and type have to be unique.  Normally the
> > only record per customer will be of type 'default', but if for instance
> > the customer wants a different billing address I would add in a second
> > type='billing' address record.
> >
> > I then want to join this table to another table, say an invoice table,
> > and I want to use the billing address if present, otherwise the default
> > address.  I do not want to create either two addresses or to put both
> > addresses on the invoice.
>
> Not sure whether a schema change is possible for you, but you might want
>   to have two tables -
>addresses (customer_id*, addr_id*, ...)
>addr_usage (customer_id*, addr_type*, addr_id)
> Add a custom trigger that ensures for every customer_id there is a valid
> row in addr_usage for each addr_type (sales, billing, shipping etc).
>
> That way you can have any mix of addresses you like, and it's explicit
> which address is for which purpose.
Interesting idea, I will consider this.

Thanks 

David

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL query

[David Goodenough]

On Friday 11 February 2005 11:41, Janning Vygen wrote:
> Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
> > I have an address table, with all the normal fields and a customer name
> > field and an address type.  There is a constraint that means that the
> > combination of customer and type have to be unique.  Normally the
> > only record per customer will be of type 'default', but if for instance
> > the customer wants a different billing address I would add in a second
> > type='billing' address record.
> >
> > I then want to join this table to another table, say an invoice table,
> > and I want to use the billing address if present, otherwise the default
> > address.  I do not want to create either two addresses or to put both
> > addresses on the invoice.
> >
> > I could do this by doing a select * from addresses where customer = ?
> > and type = 'billing', looking to see if there is a result row and if not
> > repeating the query with type = 'default', but that seems inelegant to
> > me.
> >
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > address by preference, but I am then dependant on the sort order
> > of the particular type values I am selecting from.
>
> don't think "vertical" (adresses in rows), think "horizontal" (adresses in
> columns), like this:
>
> SELECT
>   c.*,
>   COALESCE(a1.street, a2.street) AS street,
>   COALESCE(a1.zip, a2.zip) AS zip,
>   COALESCE(a1.town, a2.town) AS town
> FROM
>   customer AS c
>   LEFT JOIN adresses AS a1 USING (customer_id)
>   LEFT JOIN adresses AS a2 USING (customer_id)
> WHERE
>   a1.type = default
>   AND a2.type = 'billing'
>
> i just type the and did not tested it. the trick is to join adresses
> multiple times and get the right data with COALESCE function which returns
> the first value which is NOT NULL.
>
> If you still have difficulties, please send your schema.
>
> kind regards,
> janning

Lateral thinking always did appeal to me.  I will look into this further.

Thanks 

David

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] SQL query

[David Goodenough]

On Friday 11 February 2005 13:39, Bruno Wolff III wrote:
> On Fri, Feb 11, 2005 at 11:07:24 +,
>
>   David Goodenough <[EMAIL PROTECTED]> wrote:
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > address by preference, but I am then dependant on the sort order
> > of the particular type values I am selecting from.
>
> You can order by boolean expressions such as type = 'billing'.
> You can use that with LIMIT or DISTINCT ON to get just the address you
> want.

Tried this, and got a rather un-intuative answer.  If you have two relevant
entries (one billing, the other default) and you:-

 order by type = 'billing' limit 1

you get the default one, if you:-

 order by type != 'billing' limit 1

you get the billing one.

However:-

  order by type = 'billing' DESC limit 1 

does get you the billing one.

It makes sense in that false == 0 and true == 1 in many languages 
and 0 sorts before 1, but it still feels wrong.

I had not realised I could use a comparison like this in order by.

Thanks

David

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] size in bytes of a table?

[Mark Harrison]

I'd like to present some statistics on our database tables,
showing the number of rows and approximate amount of data
in bytes, e.g. something like this from one of our other
databases:
tasks  (546916 Kb,62018 rows)
Exact numbers don't matter, I just want to present a feel
for the amount of data we have.
Thanks!
Mark
--
Mark Harrison
Pixar Animation Studios
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] PostgreSQL Features

[Bruce Momjian]

Bruno Almeida do Lago wrote:
> Hi,
> 
> 
> 
> I'd like to know if there is something like "insert /*+ append */ into"
> and materialized views (from Oracle) on PostgreSQL.

We have SELECT INTO if that helps.

You can do materialized views and use triggers or rules to keep the
materialized view current.

--
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] test datatype for ANY

[NosyMan]

Hi there,

How can I test the type of a parameter passed to a function via ANY data type? 
I want something like this:

CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
BEGIN
IF "_param IS OF INTEGER TYPE" THEN
-- do something with INTEGER
END IF;
IF "param is of BOOLEAN TYPE" THEN
-- do something with BOOLEAN
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;


Thanks,
Nosy


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] size in bytes of a table?

[Michael Fuhr]

On Fri, Feb 11, 2005 at 10:20:47AM -0800, Mark Harrison wrote:

> I'd like to present some statistics on our database tables,
> showing the number of rows and approximate amount of data
> in bytes, e.g. something like this from one of our other
> databases:
> 
> tasks  (546916 Kb,62018 rows)

Take a look at contrib/dbsize and contrib/pgstattuple.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] size in bytes of a table?

[Tom Lane]

Mark Harrison <[EMAIL PROTECTED]> writes:
> I'd like to present some statistics on our database tables,
> showing the number of rows and approximate amount of data
> in bytes, e.g. something like this from one of our other
> databases:

> tasks  (546916 Kb,62018 rows)

> Exact numbers don't matter, I just want to present a feel
> for the amount of data we have.

Do a vacuum and then SELECT relname, relpages * 8K, reltuples FROM pg_class

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] ERROR: control reached end of function without RETURN

[Ignacio Colmenero]

Hi all.

I created a function, using EMS:

CREATE OR REPLACE
FUNCTION "geo_schema"."search_geo" () RETURNS SETOF varchar
AS

$body$

declare x_longlat
cursor for select long_lat from h2s ;

declare
var_longlat public.geometry ;

declare x_id
varchar ;

begin


open x_longlat ;


<>


loop


fetch x_longlat into var_longlat ;


if not found then

   
close x_longlat ;

   
exit ;


end if ;


select field_id from geo_table


where within(var_longlat, geo_polygon) limit 1 into x_id ;


if x_id <> '' then

   
return next x_id ;


end if ;


end loop ;

end ;

$body$

LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

When I debug it (using EMS tools), it
works fine, and I get 73 rows, but when I run it like: 

select * from
search_geo() ;

I get the error: ERROR:  control reached end of function without
RETURN

 

What can be wrong?

 

TIA

Ignacio.

 

---

Ignacio Colmenero

Software Development

Micotan Software Company Ltd.

 

Re: [GENERAL] test datatype for ANY

[Michael Fuhr]

On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
> 
> How can I test the type of a parameter passed to a function via ANY data 
> type? 
> I want something like this:
> 
> CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
>   BEGIN
>   IF "_param IS OF INTEGER TYPE" THEN
>   -- do something with INTEGER
>   END IF;

PostgreSQL has an undocumented IS OF construct:

http://archives.postgresql.org/pgsql-general/2005-01/msg00398.php

Example:

  IF param IS OF (integer) THEN
  -- do integer stuff
  ELSIF param IS OF (boolean) THEN
  -- do boolean stuff
  END IF;

Since IS OF is undocumented, I'd be careful about using it.  I don't
know what plans the developers have for it, but I doubt they'll
feel sorry for you if your code breaks because they removed it or
changed its behavior.

See also the coltype() function I posted as part of the same thread
that mentioned IS OF:

http://archives.postgresql.org/pgsql-general/2005-01/msg00390.php

Using coltype(), the above code would look like this:

  IF coltype(param) = 'integer'::regtype THEN
  -- do integer stuff
  ELSIF coltype(param) = 'boolean'::regtype THEN
  -- do boolean stuff
  END IF;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] ERROR: control reached end of function without RETURN

[Tom Lane]

"Ignacio Colmenero" <[EMAIL PROTECTED]> writes:
> I get the error: ERROR:  control reached end of function without RETURN

> What can be wrong?

You don't have a RETURN statement.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] test datatype for ANY

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
>> I want something like this:
>> 
>> CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$

> PostgreSQL has an undocumented IS OF construct:

... which won't help since IS OF is a parse-time test, and he needs a
run-time one.  However, he'll never get that far anyway since plpgsql
doesn't allow ANY parameters.  AFAIK the *only* way you could write such
a function is in C (using get_fn_expr_argtype()).

There are some limited cases you could handle in plpgsql using the
polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
no concept of a run-time type test.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] ERROR: control reached end of function without RETURN

[Ignacio Colmenero]

Thanks Tom.
I didn't know I needed a RETURN statement at the end. I assumed that the
"return next" statement would be enough. It works fine now.

---
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: February 11, 2005 12:28 PM
To: Ignacio Colmenero
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ERROR: control reached end of function without RETURN


"Ignacio Colmenero" <[EMAIL PROTECTED]> writes:
> I get the error: ERROR:  control reached end of function without RETURN

> What can be wrong?

You don't have a RETURN statement.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] test datatype for ANY

[Michael Fuhr]

On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:

> There are some limited cases you could handle in plpgsql using the
> polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
> no concept of a run-time type test.

Eh?  What am I misunderstanding then?  The following done in 8.0.1:

CREATE FUNCTION argtype(param anyelement) RETURNS text AS $$
BEGIN
IF param IS OF (integer) THEN
RETURN 'integer';
ELSIF param IS OF (numeric) THEN
RETURN 'numeric';
ELSIF param IS OF (boolean) THEN
RETURN 'boolean';
ELSIF param IS OF (text) THEN
RETURN 'text';
ELSIF param IS OF (date) THEN
RETURN 'date';
END IF;

   RETURN 'something else';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT argtype(1);
 argtype 
-
 integer

SELECT argtype(1.2);
 argtype 
-
 numeric

SELECT argtype('test'::text);
 argtype 
-
 text

SELECT argtype(true);
 argtype 
-
 boolean

CREATE TABLE foo (id integer, foodate date);
INSERT INTO foo VALUES (1, current_date);
SELECT argtype(id) AS idtype, argtype(foodate) AS foodatetype FROM foo;
 idtype  | foodatetype 
-+-
 integer | date

SELECT argtype(x) FROM (SELECT foodate FROM foo) AS s(x);
 argtype 
-
 date

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] test datatype for ANY

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:
>> There are some limited cases you could handle in plpgsql using the
>> polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
>> no concept of a run-time type test.

> Eh?  What am I misunderstanding then?  The following done in 8.0.1:

> CREATE FUNCTION argtype(param anyelement) RETURNS text AS $$
> BEGIN
> IF param IS OF (integer) THEN
>   RETURN 'integer';
> ELSIF param IS OF (numeric) THEN
>   RETURN 'numeric';
> ELSIF param IS OF (boolean) THEN
>   RETURN 'boolean';
> ELSIF param IS OF (text) THEN
>   RETURN 'text';
> ELSIF param IS OF (date) THEN
>   RETURN 'date';
> END IF;

>RETURN 'something else';
> END;
> $$ LANGUAGE plpgsql IMMUTABLE;

[ thinks about that for awhile... ]  Oh, I see.  The reason this appears
to work is that plpgsql compiles a separate version of the function for
each actual parameter datatype that is used in a given session.  So in
your example, you get a separate version for integer, numeric, etc.
Within each such version IS OF yields constants, but it "works right"
anyway.

I'm not sure if you can actually tell the difference between this
behavior and a true runtime test; except maybe that the backend would
get a bit bloated if you tried it on hundreds of different types in one
session.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Understanding EXPLAIN ANALYZE output

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Martijn van Oosterhout  writes:

> To be honest, I'm not sure this a real problem. You could simply label
> the first columns a rownumber and a depth number.

[See below ]

> At the moment people are talking about parsing strings to get the
> output. That output has the same issues as what's being proposed here,
> we're just saving the parsing step.

Yes, but whenever I need to parse "prose", I think there's something
wrong.  The textual EXPLAIN output is fine only for short query plans.

> However, tuple based output would be quite unreadable for humans, how
> can one specify which output to return. EXPLAIN ANALYZE WITH TUPLES
> query?

Going from tuples to prose is easy; there could be several formatting
functions for that - maybe even one which outputs the plan in a way
compatible to Oracle or something else.

If someone's interested, here's my "prose parser" again, now also
calculating the nesting depth:

CREATE TYPE expl_t AS (
  id INT,
  level INT,
  type TEXT,
  relation TEXT,
  cost1 FLOAT,
  cost2 FLOAT,
  rows INT,
  width INT,
  atime1 FLOAT,
  atime2 FLOAT,
  arows INT,
  loops INT,
  cont TEXT
);

CREATE OR REPLACE FUNCTION pg_explain(TEXT) RETURNS SETOF expl_t AS $$
  my $sql = $_[0];
  my $rv = spi_exec_query('SELECT current_database()');
  my $db = $rv->{rows}[0]->{current_database};
  # Grab EXPLAIN output
  use strict;
  use warnings;
  use DBI;
  my $dbh = DBI->connect("DBI:Pg:dbname=$db", "", "", {
AutoCommit => 0,
PrintError => 0,
RaiseError => 1,
  });
  my $sth = $dbh->prepare("EXPLAIN ANALYZE $sql");
  $sth->execute();
  my @res = ();
  my @nm = qw(type relation cost1 cost2 rows width atime1 atime2 arows loops);
  my $cnt = 0;
  my @plen = (0);
  while (my $res = $sth->fetchrow_arrayref) {
$cnt++;
my @a = $res->[0] =~ m{^
  (?:(\s+)->\s\s)?  # Prefix
  (\S+(?:\s\S+)?)   # Operation
  (?:\son\s(\S+)(?:\s\S+)?)?# on table [alias]
  \s\s  # Estimations:
  \(cost=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\swidth=(\d+)\)
  \s# Actual values:
  \(actual\stime=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\sloops=(\d+)\)
$}x;
unless (@a) {
  $_ = $res->[0];
  next if /^Total runtime: \d+\.\d+ ms$/;
  s/^\s+//;
  $res[$#res]->{cont} = $_;
  next;
}
my $pref = shift @a || "";
my $pl = length $pref;
# Process prefix
my $lvl = 0;
if ($pl > $plen[$#plen]) {
  push @plen, $pl;
  $lvl = $#plen;
} else {
  for my $ix (0 .. $#plen) {
next unless $plen[$ix] == $pl;
$lvl = $ix;
last;
  }
}
my %elt = (id => $cnt, level => $lvl);
$elt{$nm[$_]} = $a[$_] for (0..$#nm);
push @res, \%elt;
  }
  $dbh->disconnect;
  return [EMAIL PROTECTED];
$$ LANGUAGE "plperlu";


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Hello...

[Cristian Prieto]

Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
Linux Fedora Core 3.
The tables and objects in the database schema are working ok...
I was just writing that function:

--- Begin of my function ---
CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varchar,
varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
varchar, varchar, varchar, varchar, varchar) RETURNS integer AS
$body$
DECLARE
nombre ALIAS FOR $1;
apellido ALIAS FOR $2;
pass ALIAS FOR $3;
e_mail ALIAS FOR $4;
sexo ALIAS FOR $5;
fecha_creacion ALIAS FOR $6;
status_user ALIAS FOR $7;
edad ALIAS FOR $8;
pais_origen ALIAS FOR $9;
ocupacion_user ALIAS FOR $10;
estado_civil_user ALIAS FOR $11;
ip ALIAS FOR $12;
comentario_user ALIAS FOR $13;
intereses_user  ALIAS FOR $14;
fotoALIAS FOR $15;
returnValue integer;
idusr INTEGER := nextval('users_iduser_seq');

BEGIN
BEGIN
INSERT INTO users (iduser,
firstname,lastname,password,email,sex,datecreate,status,age,pais,ocupacion,e
stado_civil,remote_ip,comentario,intereses,fotoext) VALUES (idusr,
nombre,apellido,pass,e_mail,sexo,fecha_creacion,status_user,edad,pais_origen
,ocupacion_user,estado_civil_user,ip,comentario_user,intereses_user,foto);
EXCEPTION
WHEN unique_violation then
return 0;
END;
RETURN idusr;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
--- End of my functions ---

When I try to execute that function I get the following error:

ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist

My table structure is as following:

--- Begin table structure ---
CREATE TABLE "public"."users" (
  "iduser" SERIAL, 
  "firstname" VARCHAR(50) DEFAULT ''::character varying NOT NULL, 
  "lastname" VARCHAR(50) DEFAULT ''::character varying NOT NULL, 
  "password" VARCHAR(50) DEFAULT ''::character varying NOT NULL, 
  "email" VARCHAR(80) DEFAULT ''::character varying NOT NULL, 
  "datecreate" DATE DEFAULT '1970-01-01'::date NOT NULL, 
  "status" SMALLINT DEFAULT (0)::smallint NOT NULL, 
  "age" SMALLINT DEFAULT (0)::smallint NOT NULL, 
  "lastlogin" DATE DEFAULT '1970-01-01'::date NOT NULL, 
  "pais" VARCHAR(100) DEFAULT ''::character varying NOT NULL, 
  "ciudad" VARCHAR(100), 
  "car" CHAR(2), 
  "where_inet" VARCHAR(50), 
  "empleados" VARCHAR(50), 
  "inet" VARCHAR(50), 
  "pay" VARCHAR(50), 
  "cada_cuanto" VARCHAR(50), 
  "ocupacion" VARCHAR(150), 
  "estado_civil" VARCHAR(50), 
  "paish" VARCHAR(50), 
  "remote_ip" VARCHAR(50), 
  "zipcode" VARCHAR(50) DEFAULT ''::character varying, 
  "votos" BIGINT DEFAULT (0)::bigint NOT NULL, 
  "comentario" VARCHAR(4000), 
  "puntaje" BIGINT DEFAULT (0)::bigint NOT NULL, 
  "sex" SMALLINT DEFAULT 0 NOT NULL, 
  "intereses" VARCHAR(150) DEFAULT ''::character varying, 
  "paisorigen" VARCHAR(100), 
  "fotoext" VARCHAR(10), 
  "promedio" REAL DEFAULT 0.00, 
  CONSTRAINT "users_email_key" UNIQUE("email"), 
  CONSTRAINT "users_iduser_key" UNIQUE("iduser"), 
  CONSTRAINT "users_pkey" PRIMARY KEY("iduser", "email")
) WITH OIDS;
--- End table structure ---

What is wrong with my function or parameters? I was just looking around a
lot of places in internet and I could not get any answer, help please!



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Python Driver

[Alex Turner]

Funny you should say that - the system I am developing has a similar
system, although not as fully developed.  I am now having trouble with
psycopg locking up my database somehow.  It's almost like there are
outstanding locks on objects in the database that are preventing other
threads operating.  Talk about a frustrating week.

Alex Turner
netEconomist


On Fri, 11 Feb 2005 08:58:21 -0600, James Thompson <[EMAIL PROTECTED]> wrote:
> On Thursday 10 February 2005 08:56 pm, Alex Turner wrote:
> > Just a small warning for those people using python with postgresql:
> > pysgresql and psycopg are very different animals.  You cannot drop in
> > one as a replacement for the other, even though both 'claim' to be DB
> > API 2.0 compliant.
> 
> None of the python postgresql drivers are drop in replacements for each other.
> Some drivers, such as popy return all data as strings, some have broken
> fetchmany support, each IIRC return different exceptions in different
> situations.  Pygresql has its own API that I believe predates DBAPI 2.0 and
> support for the standard API was added at a later date.
> 
> shameless plug.
> 
> One feature of gnuenterprize.org's common library is a datasource system that
> uses a single connections.conf file that contains  entries like
> 
> [devel]
> comment = Development DB
> provider = psycopg
> host = rdbms.gnuenterprise.org
> dbname = devel
> 
> to setup a connection to a database named "devel".  Providers can be any of
> the 4 postgresql drivers, oracle, db2, ingres, mysql,  ado, odbc, etc, etc.
> Most if not all the dbsig 2 drivers are supported.  Other formats such a dbf
> and csv are supported to varying degrees.  You can switch out databases by
> editing that one file.  Providers are not limited to just databases either,
> our application server is just another provider in our system.  Differences
> between providers are handled transparently to the developer.
> 
> Take Care,
> James
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Error from database

[Alex Turner]

I am getting an error from pygresql:

Exception pg.InternalError: 'Connection already closed' in > ignored

I have already mailed the pysgresql list but without much luck.  I'm
hoping somebody here might be able to help me figure out whats going
on with this.

I have tried switching to psycopg, but it' locking up my tables
instead :(.  Ultimately I think I want to work with psycopg, but I
don't know enough to figure out who has locks on what.

Thanks,

Alex Turner
netEconomist

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Python Driver

[Michael Fuhr]

On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
>
> I am now having trouble with psycopg locking up my database somehow.
> It's almost like there are outstanding locks on objects in the database
> that are preventing other threads operating.

Do you mean the Python script itself is locked up, or just that
locks in the database are causing certain transactions to block?
Have you looked at pg_locks?  Are locks being held by idle uncommitted
transactions?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Python Driver

[Alex Turner]

What does the column 'relation' in pg_locks key to (Is there any docs
on the website for this?)

Alex


On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
> >
> > I am now having trouble with psycopg locking up my database somehow.
> > It's almost like there are outstanding locks on objects in the database
> > that are preventing other threads operating.
> 
> Do you mean the Python script itself is locked up, or just that
> locks in the database are causing certain transactions to block?
> Have you looked at pg_locks?  Are locks being held by idle uncommitted
> transactions?
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 
>

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Python Driver

[Alex Turner]

pg_locks - awesome - I will check it out...

I think it's uncommitted transactions that are causing the problem. 
The original code was written very transactionaly.

Alex Turner
netEconomist


On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
> >
> > I am now having trouble with psycopg locking up my database somehow.
> > It's almost like there are outstanding locks on objects in the database
> > that are preventing other threads operating.
> 
> Do you mean the Python script itself is locked up, or just that
> locks in the database are causing certain transactions to block?
> Have you looked at pg_locks?  Are locks being held by idle uncommitted
> transactions?
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 
>

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Python Driver

[Michael Fuhr]

On Fri, Feb 11, 2005 at 04:24:17PM -0500, Alex Turner wrote:
>
> pg_locks - awesome - I will check it out...

See also pg_stat_activity.  If you don't see anything in the
current_query column then edit postgresql.conf and set
stats_command_string = true, then restart the database.  With
this configuration you'll be able to see what each connection
is doing.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Python Driver

[Michael Fuhr]

On Fri, Feb 11, 2005 at 04:26:04PM -0500, Alex Turner wrote:
>
> What does the column 'relation' in pg_locks key to (Is there any docs
> on the website for this?)

See the "System Catalogs" chapter in the documentation (substitute
your version of PostgreSQL in the link):

http://www.postgresql.org/docs/8.0/static/catalogs.html

"relation" refers to an oid in pg_class.  An easy way to convert
it to a relation name is to cast it to regclass:

SELECT relation::regclass, * FROM pg_locks;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong libperl.so

[Sven Willenberger]

On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
> On Fri, Feb 11, 2005 at 11:10:15AM -0500, Sven Willenberger wrote:
> > On Fri, 2005-02-11 at 16:46 +0100, Palle Girgensohn wrote:
> > > --On fredag, februari 11, 2005 10.24.22 -0500 Sven Willenberger 
> > > <[EMAIL PROTECTED]> wrote:
> 
> > > > FreeBSD 4.10
> > > > Postgresql 7.4.7
> > > > Perl 5.8.6_2 (from ports)
> 
> > > > When building databases/p5-postgresql-plperl the resultant plperl.so
> > > > (/usr/local/lib/postgresql/plperl.so) links to the libperl.so
> > > > in /usr/lib instead of /usr/local/lib/perl5/5.8.6/mach/CORE/.
> 
> > > > ldd /usr/local/lib/postgresql/plperl.so
> > > > /usr/local/lib/postgresql/plperl.so:
> > > > libperl.so => /usr/lib/libperl.so (0x2810b000)
> > > > libm.so.2 => /usr/lib/libm.so.2 (0x281a3000)
> > > > libcrypt.so.2 => /usr/lib/libcrypt.so.2 (0x281be000)
> > > > libutil.so.3 => /usr/lib/libutil.so.3 (0x281d7000)
> 
> > > > the configure script used by postgresql itself tests for the lib
> > > > directory via:
> > > >|> perl -MConfig -e 'print $Config{archlibexp}'
> > > > /usr/local/lib/perl5/5.8.6/mach
> 
> > > > so it appears to find it ... is something in ports overriding this
> > > > location or is there something I can -Define to have it use the correct
> > > > libperl.so?
> 
> > > I'd say this is a bug in the perl port. Just like it relinks the perl 
> > > binary, it should ultimately relink the libperl.so file.
> 
> I don't think so.  All symlinking that is done with /usr/bin/perl* does
> not disrupt functioning of the base system perl, only modifies the
> defaults used.  One can still do /usr/bin/perl5.005_03 and it will work
> perfectly.  Destroying /usr/lib/libperl.so will change that.
> 
> So I'd say, it is one of two things:
> 
> 1. _Either_ Sven has LD_LIBRARY_PATH set in his or global environment in
>such a way that it includes /usr/lib in there.  If this is the case,
>removing it would solve the problem.  The reason to not have /usr/lib
>in LD_LIBRARY_PATH and for the described error to occur is two-fold:
>first, /usr/lib is already in ldconfig cache, so having it in
>LD_LIBRARY_PATH has no purpose;  secondly, LD_LIBRARY_PATH takes
>precedence over any libraries linked with explicit directory
>information, which is an intended behavior.
> 
This is not the case, so this one can be ruled out as a cause.

> 2. _Or_ plperl does not go all the way to be a conformant perl-embedding
>application.  It looks at $Config{archlibexp}, but it does not follow
>directions described in perlembed(1).  In this case it's linking
>should be fixed to respect that.
> 
> \Anton.

This does seem to be the case. I built postgresqql from source this time
rather than ports with ./configure --with-perl --with-openssl and, as
you point out, the congigure does find its way to the CORE directory but
the end product still links to the /usr/lib/libperl.so location. 

The output from the lines building plperl are:

plperl.c: In function `compile_plperl_function':
plperl.c:541: warning: cast to pointer from integer of different size
plperl.c:730: warning: cast from pointer to integer of different size
gcc -O2 -fno-strict-aliasing -fpic -DPIC -I.
-I/usr/local/lib/perl5/5.8.6/mach/CORE -I../../../src/include   -c -o
eloglvl.o eloglvl.c
/usr/bin/perl /usr/local/lib/perl5/5.8.6/ExtUtils/xsubpp
-typemap /usr/local/lib/perl5/5.8.6/ExtUtils/typemap SPI.xs >SPI.c
gcc -O2 -fno-strict-aliasing -fpic -DPIC -I.
-I/usr/local/lib/perl5/5.8.6/mach/CORE -I../../../src/include   -c -o
SPI.o SPI.c
ar cr libplperl.a `lorder plperl.o eloglvl.o SPI.o | tsort`
ranlib libplperl.a
gcc -O2 -fno-strict-aliasing -fpic -DPIC -shared
-Wl,-x,-soname,libplperl.so.0 plperl.o eloglvl.o SPI.o
-L../../../src/port -Wl,-E
-L/usr/local/lib /usr/local/lib/perl5/5.8.6/mach/auto/DynaLoader/DynaLoader.a 
-L/usr/local/lib/perl5/5.8.6/mach/CORE -lperl -lm -lcrypt -lutil  
-R/usr/local/pgsql/lib -o libplperl.so.0
rm -f libplperl.so
ln -s libplperl.so.0 libplperl.so

So somewhere in there it is preferentially picking the /usr/lib location
rather than the mach/CORE location. I am cc'ing the postgresql list on
this as well; at any rate it does not seem to be a port-specific or
perl-installation specific error here.

Sven Willenberger


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Understanding EXPLAIN ANALYZE output

[Martijn van Oosterhout]

On Fri, Feb 11, 2005 at 03:50:05PM +0100, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Martijn van Oosterhout  writes:
> > At the moment people are talking about parsing strings to get the
> > output. That output has the same issues as what's being proposed here,
> > we're just saving the parsing step.
> 
> Yes, but whenever I need to parse "prose", I think there's something
> wrong.  The textual EXPLAIN output is fine only for short query plans.

I agree. I'm wondering if it is possible to write a function to produce
the alterbate output. I havn't looked at that code in a while.

> > However, tuple based output would be quite unreadable for humans, how
> > can one specify which output to return. EXPLAIN ANALYZE WITH TUPLES
> > query?
> 
> Going from tuples to prose is easy; there could be several formatting
> functions for that - maybe even one which outputs the plan in a way
> compatible to Oracle or something else.
> 
> If someone's interested, here's my "prose parser" again, now also
> calculating the nesting depth:



There's one corner case you need to make sure you handle. In the plan
that started this thread there's a query node marked (never executed).
That will affect yout regex a bit. an that case you should probably
return NULLs. (It might do that, I havn't run the code through
carefully).

Case in point to why it should be an internal function.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpTH2TVFpGZB.pgp
Description: PGP signature

Re: [GENERAL] Hello...

[Martijn van Oosterhout]

On Fri, Feb 11, 2005 at 02:56:09PM -0600, Cristian Prieto wrote:
> Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
> Linux Fedora Core 3.
> The tables and objects in the database schema are working ok...
> I was just writing that function:

Here's the function definition:

> CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varchar,
> varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
> varchar, varchar, varchar, varchar, varchar) RETURNS integer AS

As here's your error message:

> ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", "unknown", "unknown") does not exist

It's looking for a function which accepts and integer in the 7th column
you havn't define one. Check your function call...

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpsWV3zxZRg0.pgp
Description: PGP signature

[GENERAL] regular expressions in query

[fiona]

My database table holds phone numbers that may contain characters other 
than digits (that's not a problem in itself).

I want to be able to apply a regular expression (to ignore all 
characters except digits) to the attribute 'phone' first and then for 
the ILIKE to compare
the result to $telephone. I can't find any way of applying the RE to phone.
My current query without the RE is as follows:

SELECT telephone FROM addresses WHERE id = user_id AND phone ILIKE 
'%".addslashes($telephone)."%'"  
I want to do something like:  AND phone([^[:digit:]]) ILIKE $telephone
But this doesn't work.
Any ideas?
--
Get Thunderbird  


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Hello...

[Tom Lane]

Martijn van Oosterhout  writes:
> Here's the function definition:

>> CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varch=
> ar,
>> varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
>> varchar, varchar, varchar, varchar, varchar) RETURNS integer AS

> As here's your error message:

>> ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknow=
> n",
>> "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
>> "unknown", "unknown", "unknown", "unknown", "unknown") does not exist

> It's looking for a function which accepts and integer in the 7th column
> you havn't define one. Check your function call...

The function call probably looks like ..., 42, ... and since integer
doesn't implicitly cast to smallint, it doesn't match.  It would be
smarter to declare the function as taking integer; if you really want
it to take smallint, you'll need to quote or cast the arguments.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] file descriptors

[David Parker]

We have started 
getting the error
 
   
FATAL:  terminating connection due to administrator 
command
in some of our 
processes. Searching in the archives, I gather that this is caused by a SIGTERM, 
and might be coming from a ulimit problem.
 
We are running 
Solaris 9/Intel, and the ulimit for nofiles for the database owner process is 
256. I suspect this needs to be set to "unlimited", which I don't think should 
cause a problem on Solaris (?).
 
Is there any rule of 
thumb for determining how many file descriptors a given postmaster would use, 
e.g., a  way to find out how many data files exist on the server, and/or an 
estimate for how many of them would have to be open at any given time? Do ALL of 
the data files get kept open, or do they get opened and 
closed?
Thanks.
- 
DAP--David 
Parker    Tazz Networks    (401) 
709-5130 
 

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong libperl.so

[Tom Lane]

Sven Willenberger <[EMAIL PROTECTED]> writes:
>> 2. _Or_ plperl does not go all the way to be a conformant perl-embedding
>> application.  It looks at $Config{archlibexp}, but it does not follow
>> directions described in perlembed(1).  In this case it's linking
>> should be fixed to respect that.

You probably want to stick an rpath into the plperl.so library.  PG 8.0
does that by default, but for 7.4 I recommend something like this:

RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/GNUmakefile,v
retrieving revision 1.10
diff -r1.10 GNUmakefile
18c18
< override CFLAGS := $(filter-out -Wall -Wmissing-declarations 
-Wmissing-prototypes, $(CFLAGS))
---
> override CFLAGS := $(filter-out -Wall -Wmissing-declarations 
> -Wmissing-prototypes, $(CFLAGS), -Wl,-rpath,$(perl_archlibexp)/CORE) 

(The above is taken from the Red Hat RPMs and is known to work on Linux,
but I can't swear that it works on any other platform.)

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong libperl.so

[Sven Willenberger]

On Fri, 2005-02-11 at 23:20 +0100, Anton Berezin wrote:
> On Fri, Feb 11, 2005 at 11:09:35PM +0100, Anton Berezin wrote:
> > On Fri, Feb 11, 2005 at 04:41:55PM -0500, Sven Willenberger wrote:
> > > On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
> > > > On Fri, Feb 11, 2005 at 11:10:15AM -0500, Sven Willenberger wrote:
> > > > > On Fri, 2005-02-11 at 16:46 +0100, Palle Girgensohn wrote:
> > > > > > --On fredag, februari 11, 2005 10.24.22 -0500 Sven Willenberger 
> > > > > > <[EMAIL PROTECTED]> wrote:
> > > > 
> > > > > > > FreeBSD 4.10
> > > > > > > Postgresql 7.4.7
> > > > > > > Perl 5.8.6_2 (from ports)
> > > > 
> > > > > > > When building databases/p5-postgresql-plperl the resultant 
> > > > > > > plperl.so
> > > > > > > (/usr/local/lib/postgresql/plperl.so) links to the libperl.so
> > > > > > > in /usr/lib instead of /usr/local/lib/perl5/5.8.6/mach/CORE/.
> > > > 
> > > > > > > ldd /usr/local/lib/postgresql/plperl.so
> > > > > > > /usr/local/lib/postgresql/plperl.so:
> > > > > > > libperl.so => /usr/lib/libperl.so (0x2810b000)
> > > > > > > libm.so.2 => /usr/lib/libm.so.2 (0x281a3000)
> > > > > > > libcrypt.so.2 => /usr/lib/libcrypt.so.2 (0x281be000)
> > > > > > > libutil.so.3 => /usr/lib/libutil.so.3 (0x281d7000)
> > 
> > > > 2. _Or_ plperl does not go all the way to be a conformant perl-embedding
> > > >application.  It looks at $Config{archlibexp}, but it does not follow
> > > >directions described in perlembed(1).  In this case it's linking
> > > >should be fixed to respect that.
> > 
> > > This does seem to be the case. I built postgresqql from source this time
> > > rather than ports with ./configure --with-perl --with-openssl and, as
> > > you point out, the congigure does find its way to the CORE directory but
> > > the end product still links to the /usr/lib/libperl.so location. 
> > 
> > Alright.  It is not plperl folks fault, shared libraries and binaries do
> > things differently.  Consider:
> > 
> > $ cat >binary.c
> > int main() {}
> > ^D
> > 
> > $ gcc binary.c -Wl,-E -L/usr/local/lib \
> > /usr/local/lib/perl5/5.6.2/mach/auto/DynaLoader/DynaLoader.a \
> > -L/usr/local/lib/perl5/5.6.2/mach/CORE -lperl -lm -lcrypt -lutil \
> > -o binary
> > 
> > $ ldd ./binary
> > ./binary:
> > libperl.so => /usr/lib/libperl.so (0x28066000)
> > libm.so.2 => /usr/lib/libm.so.2 (0x280fe000)
> > libcrypt.so.2 => /usr/lib/libcrypt.so.2 (0x28119000)
> > libutil.so.3 => /usr/lib/libutil.so.3 (0x28132000)
> > libc.so.4 => /usr/lib/libc.so.4 (0x2813b000)
> 
> G.  What I wrote does not make any sense at all.  :-(
> 
> It is plperl fault after all,
> 
> perl -MExtUtils::Embed -le ldopts
> 
> clearly states
> 
>   -Wl,-R/usr/local/lib/perl5/5.6.2/mach/CORE -Wl,-E  -L/usr/local/lib
>   /usr/local/lib/perl5/5.6.2/mach/auto/DynaLoader/DynaLoader.a
>   -L/usr/local/lib/perl5/5.6.2/mach/CORE -lperl -lm -lc -lcrypt -lutil
> 
> Note the -R here, and the absense of it in the lines you posted.
> 
> Anton 'need more coffee or more sleep' Berezin

Ok, I have found a couple lines in configure (4354-4356) and
config/perl.m4 (31-33) that confuse me a bit as it seems that their sole
purpose is to actually strip *out* the
-R/usr/local/lib/perl5/5.8.6/mach/CORE portion of the linker args:

pgac_tmp1=`$PERL -MExtUtils::Embed -e ldopts`
pgac_tmp2=`$PERL -MConfig -e 'print $Config{ccdlflags}'`
perl_embed_ldflags=`echo X"$pgac_tmp1" | sed "s/^X//;s%$pgac_tmp2%%"`

now pgac_tmp1 contains what should really be the ld args for the final
build/linking, pgac_tmp2 contains
-Wl,-R/usr/local/lib/perl5/5.8.6/mach/CORE

it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
leaving the system to use the default perl libperl.so.

when I modified perl_embed_ldflags to not remove the -R line, the
compile works and links to the proper libperl.so

Sven

P.S. From this point forward I will direct any followups to just the
postgresql mailing list as it does not appear to FreeBSD specific at
this point).


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] file descriptors

[Tom Lane]

"David Parker" <[EMAIL PROTECTED]> writes:
> We have started getting the error
>FATAL:  terminating connection due to administrator command
> in some of our processes. Searching in the archives, I gather that this
> is caused by a SIGTERM, and might be coming from a ulimit problem.

It is coming from a SIGTERM, but I'm not aware of any platforms that
respond to exceeding the ulimit open-files limit by SIGTERM'ing the
process.  I think you're barking up the wrong tree.

> We are running Solaris 9/Intel, and the ulimit for nofiles for the
> database owner process is 256. I suspect this needs to be set to
> "unlimited", which I don't think should cause a problem on Solaris (?).

I think it *would* cause a problem, unless Solaris can support unlimited
numbers of open files --- we have certainly seen PG eat all available
file table slots on other kernels.  I don't recommend raising nofiles.
The backends are perfectly capable of working within the nofiles limit
you set, and 256 seems high enough to avoid thrashing.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] file descriptors

[David Parker]

OK, well that's good to know. You mentioned ulimit in

http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php 

which ulimit parameters were you thinking of? That post is what set me
barking up 
this tree ;-) The only other thing not set to "unlimited" is stack,
which is set to
8480 for the database owner on this system (not sure where that number
came from).

Thanks.

- DAP

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: Friday, February 11, 2005 6:17 PM
>To: David Parker
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] file descriptors 
>
>"David Parker" <[EMAIL PROTECTED]> writes:
>> We have started getting the error
>>FATAL:  terminating connection due to administrator 
>command in some 
>> of our processes. Searching in the archives, I gather that this is 
>> caused by a SIGTERM, and might be coming from a ulimit problem.
>
>It is coming from a SIGTERM, but I'm not aware of any 
>platforms that respond to exceeding the ulimit open-files 
>limit by SIGTERM'ing the process.  I think you're barking up 
>the wrong tree.
>
>> We are running Solaris 9/Intel, and the ulimit for nofiles for the 
>> database owner process is 256. I suspect this needs to be set to 
>> "unlimited", which I don't think should cause a problem on 
>Solaris (?).
>
>I think it *would* cause a problem, unless Solaris can support 
>unlimited numbers of open files --- we have certainly seen PG 
>eat all available file table slots on other kernels.  I don't 
>recommend raising nofiles.
>The backends are perfectly capable of working within the 
>nofiles limit you set, and 256 seems high enough to avoid thrashing.
>
>   regards, tom lane
>

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong libperl.so

[Tom Lane]

Sven Willenberger <[EMAIL PROTECTED]> writes:
> it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
> leaving the system to use the default perl libperl.so.
> when I modified perl_embed_ldflags to not remove the -R line, the
> compile works and links to the proper libperl.so

It worked for you, but not necessarily for other people.  This thread
may be instructive:
http://archives.postgresql.org/pgsql-bugs/2004-11/msg00181.php

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] file descriptors

[Tom Lane]

"David Parker" <[EMAIL PROTECTED]> writes:
> OK, well that's good to know. You mentioned ulimit in
> http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php 

Well, that was a different scenario --- or at least I thought it was.
Are you seeing unwanted shutdown of the entire database cluster, or just
an individual backend?  What shows up in the postmaster log when this
happens?

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong

[Sven Willenberger]

On Fri, 2005-02-11 at 18:35 -0500, Tom Lane wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
> > leaving the system to use the default perl libperl.so.
> > when I modified perl_embed_ldflags to not remove the -R line, the
> > compile works and links to the proper libperl.so
> 
> It worked for you, but not necessarily for other people.  This thread
> may be instructive:
> http://archives.postgresql.org/pgsql-bugs/2004-11/msg00181.php
> 
>   regards, tom lane

I see they used the same solution that I did, namely remove the part of
the sed statement that removes the ccdflags frmo the ExtUtils::Embed's
output. The rpath solution you provided to patch GNUmakefile did not
work however, bailing with:

gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
-rpath, /usr/local/lib/perl5/5.8.6/mach/CORE -fpic -DPIC -I.
-I/usr/local/lib/perl5/5.8.6/mach/CORE -I../../../src/include   -c -o
plperl.o plperl.c
gcc: cannot specify -o with -c or -S and multiple compilations

(As you said it may not work on all platforms). 

At this point I will try to submit or suggest a patch for the freebsd
ports system to simply change the lines in the configure config/perl.m4
files to strip out the replacement part of the sed statement.

Thanks,

Sven


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] file descriptors

[David Parker]

It's just an individual backend. Unfortunately I don't have the postgres
log file yet, which would obviously help - I only have the application
client's log, but the people reporting the problem know they need to get
me the database log file next time it happens. I haven't been able to
repro this on my own yet.

Thanks. 

- DAP 

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: Friday, February 11, 2005 6:40 PM
>To: David Parker
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] file descriptors 
>
>"David Parker" <[EMAIL PROTECTED]> writes:
>> OK, well that's good to know. You mentioned ulimit in 
>> http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php
>
>Well, that was a different scenario --- or at least I thought it was.
>Are you seeing unwanted shutdown of the entire database 
>cluster, or just an individual backend?  What shows up in the 
>postmaster log when this happens?
>
>   regards, tom lane
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Apparent anomaly with views and unions

[Guy Rouillier]

I using 8.0.1.  I create 3 tables with these definitions:

create table t1 (serv_id varchar(50) not null);
create table t2 (serv_id varchar(50) not null);
create table t3 (serv_id varchar(50) not null);

Now I create a view like this:

create or replace view v1 as select * from t1;

Next, I attempt to update this view like this:
create or replace view v1 as select * from t1 union select * from t2;

I receive: ERROR:  cannot change data type of view column "serv_id"

Next, I drop view v1 and recreate it using the union above.  I am now
able to do the following without error:

create or replace view v1 as select * from t1 union select * from t2
union select * from t3;

So it looks like changing a view from a non-union to a union is
producing an error, but changing a view that  already contains a 2-way
union to one that contains a 3-way union is allowed (and I'm assuming
any n to n+1 union transition is also allowed.)  

Is this intentional?  It was certainly not predictable.

-- 
Guy Rouillier


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Apparent anomaly with views and unions

[Stephan Szabo]

On Fri, 11 Feb 2005, Guy Rouillier wrote:

> I using 8.0.1.  I create 3 tables with these definitions:
>
> create table t1 (serv_id varchar(50) not null);
> create table t2 (serv_id varchar(50) not null);
> create table t3 (serv_id varchar(50) not null);
>
> Now I create a view like this:
>
> create or replace view v1 as select * from t1;
>
> Next, I attempt to update this view like this:
> create or replace view v1 as select * from t1 union select * from t2;
>
> I receive: ERROR:  cannot change data type of view column "serv_id"

I'm pretty sure PostgreSQL treats the type of serv_id in the new view as
varchar with no limit rather than varchar(50). I think that's also not
entirely up to spec.

In the two to three case it already was a varchar with no limit and so the
type was the same.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong

[Tom Lane]

Sven Willenberger <[EMAIL PROTECTED]> writes:
> The rpath solution you provided to patch GNUmakefile did not
> work however, bailing with:

> gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
> -rpath, /usr/local/lib/perl5/5.8.6/mach/CORE -fpic -DPIC -I.
> -I/usr/local/lib/perl5/5.8.6/mach/CORE -I../../../src/include   -c -o
> plperl.o plperl.c
> gcc: cannot specify -o with -c or -S and multiple compilations

If that's an accurate cut-and-paste, then you let a space sneak into
the -Wl,-rpath,path construct; perhaps that's the problem?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong

[Sven Willenberger]

On Fri, 2005-02-11 at 19:54 -0500, Tom Lane wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > The rpath solution you provided to patch GNUmakefile did not
> > work however, bailing with:
> 
> > gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
> > -rpath, /usr/local/lib/perl5/5.8.6/mach/CORE -fpic -DPIC -I.
> > -I/usr/local/lib/perl5/5.8.6/mach/CORE -I../../../src/include   -c -o
> > plperl.o plperl.c
> > gcc: cannot specify -o with -c or -S and multiple compilations
> 
> If that's an accurate cut-and-paste, then you let a space sneak into
> the -Wl,-rpath,path construct; perhaps that's the problem?
> 
>   regards, tom lane

ahh, the linewrap issue of mailclients snuck in ... yes removing the
space did result in a successful build:

cc -O2 -pipe -march=pentiumpro -fno-strict-aliasing
-Wmissing-declarations, -Wl,-rpath,/usr/local/lib/perl5/5.8.6/mach/CORE
-fPIC -DPIC -I. -I/usr/local/lib/perl5/5.8.6/mach/CORE
-I../../../src/include   -c -o plperl.o plperl.c
plperl.c: In function `compile_plperl_function':
plperl.c:541: warning: cast to pointer from integer of different size
plperl.c:730: warning: cast from pointer to integer of different size
cc: -rpath: linker input file unused since linking not done
cc: /usr/local/lib/perl5/5.8.6/mach/CORE: linker input file unused since
linking not done
cc -O2 -pipe -march=pentiumpro -fno-strict-aliasing
-Wmissing-declarations, -Wl,-rpath,/usr/local/lib/perl5/5.8.6/mach/CORE
-fPIC -DPIC -I. -I/usr/local/lib/perl5/5.8.6/mach/CORE
-I../../../src/include   -c -o eloglvl.o eloglvl.c
cc: -rpath: linker input file unused since linking not done
cc: /usr/local/lib/perl5/5.8.6/mach/CORE: linker input file unused since
linking not done
/usr/bin/perl /usr/local/lib/perl5/5.8.6/ExtUtils/xsubpp
-typemap /usr/local/lib/perl5/5.8.6/ExtUtils/typemap SPI.xs >SPI.c
cc -O2 -pipe -march=pentiumpro -fno-strict-aliasing
-Wmissing-declarations, -Wl,-rpath,/usr/local/lib/perl5/5.8.6/mach/CORE
-fPIC -DPIC -I. -I/usr/local/lib/perl5/5.8.6/mach/CORE
-I../../../src/include   -c -o SPI.o SPI.c
cc: -rpath: linker input file unused since linking not done
cc: /usr/local/lib/perl5/5.8.6/mach/CORE: linker input file unused since
linking not done
ar cr libplperl.a `lorder plperl.o eloglvl.o SPI.o | tsort`
ranlib libplperl.a
cc -O2 -pipe -march=pentiumpro -fno-strict-aliasing
-Wmissing-declarations, -Wl,-rpath,/usr/local/lib/perl5/5.8.6/mach/CORE
-fPIC -DPIC -shared -Wl,-x,-soname,libplperl.so.0 plperl.o eloglvl.o
SPI.o -L../../../src/port -L/usr/local/lib -Wl,-E
-L/usr/local/lib /usr/local/lib/perl5/5.8.6/mach/auto/DynaLoader/DynaLoader.a 
-L/usr/local/lib/perl5/5.8.6/mach/CORE -lperl -lm -lcrypt -lutil  
-R/usr/local/lib -o libplperl.so.0
rm -f libplperl.so
ln -s libplperl.so.0 libplperl.so

# cd work/postgresql-7.4.7/src/pl/plperl/
# ldd ./libplperl.so
./libplperl.so:
libperl.so => /usr/local/lib/perl5/5.8.6/mach/CORE/libperl.so
(0x2810b000)
libm.so.2 => /usr/lib/libm.so.2 (0x28207000)
libcrypt.so.2 => /usr/lib/libcrypt.so.2 (0x28222000)
libutil.so.3 => /usr/lib/libutil.so.3 (0x2823b000)


I have submitted a patch containing the other solution .. for what it's
worth attached is this solution as a patch. I will leave it to the
maintainer(s) to determine the "cleaner" solution :-)

Svem
--- src/pl/plperl/GNUmakefile.orig  Wed Jan 21 14:25:11 2004
+++ src/pl/plperl/GNUmakefile   Fri Feb 11 20:06:31 2005
@@ -15,7 +15,7 @@
 
 # The code isn't clean with regard to these warnings.
 ifeq ($(GCC),yes)
-override CFLAGS := $(filter-out -Wall -Wmissing-declarations 
-Wmissing-prototypes, $(CFLAGS))
+override CFLAGS := $(filter-out -Wall -Wmissing-declarations 
-Wmissing-prototypes, $(CFLAGS), -Wl,-rpath,$(perl_archlibexp)/CORE)
 endif
 
 override CPPFLAGS := -I$(srcdir) -I$(perl_archlibexp)/CORE $(CPPFLAGS)

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Apparent anomaly with views and unions

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>> Now I create a view like this:
>> create or replace view v1 as select * from t1;
>> 
>> Next, I attempt to update this view like this:
>> create or replace view v1 as select * from t1 union select * from t2;
>> 
>> I receive: ERROR:  cannot change data type of view column "serv_id"

> I'm pretty sure PostgreSQL treats the type of serv_id in the new view as
> varchar with no limit rather than varchar(50).

This is the same issue reported here:
http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php
http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php
and is pretty closely related to this:
http://archives.postgresql.org/pgsql-bugs/2005-02/msg8.php

I was looking at fixing it yesterday.  The obvious path to a fix is to
do select_common_type across all the members of a set-operation tree
at once, rather than pairwise as we do it now.  That bothers me a bit
because it could change the semantics.  Consider for example

  SELECT float8col UNION ALL (SELECT numericcol UNION SELECT numericcol)

The final result will be float8 in any case, but I think that a strict
reading of the spec requires the righthand UNION to be done in numeric
type, before we promote to float8.  If we promote to float8 and then
union, roundoff could make formerly distinct values not distinct,
resulting in a different number of output rows.

The above is a pretty contrived case though --- in particular,
I had to write UNION ALL for the upper level, else it would do a
duplicate-elimination pass that would mask the issue anyway.  In
practice I'm not sure anyone would ever notice the difference if we
took the simple path of selecting one type across the whole tree.

Maybe it would work to resolve types only across nested nodes of exactly
the same set operator.  I haven't worked through the details of this
however.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong

[Tom Lane]

Sven Willenberger <[EMAIL PROTECTED]> writes:
> plperl.c: In function `compile_plperl_function':
> plperl.c:541: warning: cast to pointer from integer of different size
> plperl.c:730: warning: cast from pointer to integer of different size

BTW, these scare the heck out of me.  It looks to me like plperl.c is
casting a pointer to an integer in order to store it in a Perl hash.
Isn't there a better way to do that?  Can anyone check on whether plperl
works at all on 64bit arches?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] databases/p5-postgresql-plperl links to wrong

[Mike Rylander]

On Fri, 11 Feb 2005 20:40:18 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > plperl.c: In function `compile_plperl_function':
> > plperl.c:541: warning: cast to pointer from integer of different size
> > plperl.c:730: warning: cast from pointer to integer of different size
> 
> BTW, these scare the heck out of me.  It looks to me like plperl.c is
> casting a pointer to an integer in order to store it in a Perl hash.
> Isn't there a better way to do that?  Can anyone check on whether plperl
> works at all on 64bit arches?
> 

It's been working fine for me on Gentoo Linux / AMD64.

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] pg_dump dumping data in order? (used to in 7.4 but not now in 8?)

[Miles Keaton]

When I do a pg_dump, (--data-only), PG7 used to dump the data out in
order, so that all foreign-key checks worked correctly when loading
the data back in.

Now it seems with PG8 it's dumping it completely out of order (one of
my completely foreign-key join tables first!) - and I can't get it to
dump in the correct order.

Anyone know a solution to this, to get a data dump to export in a
correct order so that foreign-key checks will work when loading that
data in again?

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] test datatype for ANY

[Michael Fuhr]

On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:

> [ thinks about that for awhile... ]  Oh, I see.  The reason this appears
> to work is that plpgsql compiles a separate version of the function for
> each actual parameter datatype that is used in a given session.  So in
> your example, you get a separate version for integer, numeric, etc.

Is that the general case for polymorphic functions?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] pg_dump dumping data in order? (used to in 7.4 but not now in 8?)

[Tom Lane]

Miles Keaton <[EMAIL PROTECTED]> writes:
> When I do a pg_dump, (--data-only), PG7 used to dump the data out in
> order, so that all foreign-key checks worked correctly when loading
> the data back in.

If it did so, it was by pure luck.

In general it is not possible for pg_dump to guarantee this works;
consider circular FK constraints, or FK constraints added since the
dump occurred.  In view of that, pg_dump has never tried to ensure
that data-only dumps are in a "valid" order at all.  The usual
approach is to use --disable-triggers to short-circuit the FK
constraints during the load.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] test datatype for ANY

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:
>> [ thinks about that for awhile... ]  Oh, I see.  The reason this appears
>> to work is that plpgsql compiles a separate version of the function for
>> each actual parameter datatype that is used in a given session.  So in
>> your example, you get a separate version for integer, numeric, etc.

> Is that the general case for polymorphic functions?

AFAIR all the PLs that support polymorphism do it that way.  It's not
the only way it could be done, but it was the easiest way to bolt
polymorphism onto the existing code, which generally assumed that
every data value in the function has a fixed datatype.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] regular expressions in query

[Jeff Davis]

Try using the "~" regex matching operator instead of ILIKE.

Regards,
Jeff Davis

On Fri, 2005-02-11 at 22:21 +, fiona wrote:
> My database table holds phone numbers that may contain characters other 
> than digits (that's not a problem in itself).
> 
> I want to be able to apply a regular expression (to ignore all 
> characters except digits) to the attribute 'phone' first and then for 
> the ILIKE to compare
> the result to $telephone. I can't find any way of applying the RE to phone.
> My current query without the RE is as follows:
> 
> SELECT telephone FROM addresses WHERE id = user_id AND phone ILIKE 
> '%".addslashes($telephone)."%'"  
> I want to do something like:  AND phone([^[:digit:]]) ILIKE $telephone
> But this doesn't work.
> Any ideas?


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Apparent anomaly with views and unions

[Guy Rouillier]

Tom and Stephan, thank you both for taking the time to reply.  Further
comments inline.

Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
>> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>>> Now I create a view like this:
>>> create or replace view v1 as select * from t1;
>>> 
>>> Next, I attempt to update this view like this:
>>> create or replace view v1 as select * from t1 union select * from
>>> t2; 
>>> 
>>> I receive: ERROR:  cannot change data type of view column "serv_id"
> 
>> I'm pretty sure PostgreSQL treats the type of serv_id in the new view
>> as varchar with no limit rather than varchar(50).
> 
> This is the same issue reported here:
> http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php 
> http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php
> and is pretty closely related to this:
> http://archives.postgresql.org/pgsql-bugs/2005-02/msg8.php 

We appear to have two issues here:

(1) What is the meaning of "replace" as in replace view?
(2) What are the semantics for multiple set operations?

My original issue deals with (1).  I'm unfortunately not well versed in
the SQL spec, but from a layman's point of view, if I'm replacing a view
definition, I don't expect that to be rejected because it is
incompatible with the original view definition.  My new definition may
use entirely different set of tables than the original view.  "create or
replace view" should accomplish the same thing as "drop view; create
view".

> 
> I was looking at fixing it yesterday.  The obvious path to a fix is
> to do select_common_type across all the members of a set-operation
> tree at once, rather than pairwise as we do it now.  That bothers me
> a bit because it could change the semantics.  Consider for example   
> 
>   SELECT float8col UNION ALL (SELECT numericcol UNION SELECT
> numericcol) 

This seems to be addressing point (2).  In this particular case, I would
expect the query engine to follow the submitter's explicit directions,
since he or she provided an explicit grouping.  If no parentheses are
included, then the optimizer or query rewriter is free to do as it
wishes.

-- 
Guy Rouillier


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Apparent anomaly with views and unions

[Tom Lane]

"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> We appear to have two issues here:

> (1) What is the meaning of "replace" as in replace view?
> (2) What are the semantics for multiple set operations?

> My original issue deals with (1).  I'm unfortunately not well versed in
> the SQL spec, but from a layman's point of view, if I'm replacing a view
> definition, I don't expect that to be rejected because it is
> incompatible with the original view definition.

Well, we don't have the ability to cope with redefining a view in a way
that changes its result column list --- we don't know how to propagate
that into dependent views in any clean way.  (Now, ALTER TABLE deals
with this by allowing the change only when there are no dependent views;
maybe it'd be sensible to do the same for REPLACE VIEW.)  The immediate
issue here is that the redefinition shouldn't be seen as changing the
result column type.  The underlying cause of that hasn't got anything
to do with views per se, it has to do with the way that we impute a
result datatype to a UNION structure.

> My new definition may
> use entirely different set of tables than the original view.  "create or
> replace view" should accomplish the same thing as "drop view; create
> view".

In the cases I'm concerned about, DROP VIEW would fail unless you used
CASCADE, and the subsequent CREATE VIEW would not magically bring back
the dependent objects the CASCADE had killed.  The entire *point* of
REPLACE VIEW is that it does not have the side effect of destroying
dependent objects; and therefore it has to act in a way that ensures
the dependent objects still make sense.  So, for the moment anyway,
that means disallowing changes in the output column names and datatypes.

>> Consider for example   
>> 
>> SELECT float8col UNION ALL (SELECT numericcol UNION SELECT
>> numericcol) 

> This seems to be addressing point (2).  In this particular case, I would
> expect the query engine to follow the submitter's explicit directions,
> since he or she provided an explicit grouping.  If no parentheses are
> included, then the optimizer or query rewriter is free to do as it
> wishes.

I don't think we get to do it that way.  The way the SQL spec is
written, there is not a difference between A UNION B UNION C and
(A UNION B) UNION C.  The result is *always* built up from pairwise
UNIONs, and the semantic definition of UNION talks about combining
only two tables at a time.

This consideration doesn't stop us from fixing your problem (since in
the case you care about, all the datatypes are the same anyway) ...
but it puts rather a large crimp in Woehling's problem, which I was
hoping to fix at the same time.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend