Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Pavan Deolasee]

On Thu, Nov 22, 2012 at 11:08 AM, Ranjeet Dhumal wrote:

> Hi Tom ,
>
> Sorry but i didn't understand that If this is a bug from  postgres version
> then how the same query will be worked if i recreated the tables and with
> same version of postgres.
>
>
This could be related to the plans that are chosen by the optimizer and
this specific error might be coming only when a certain plan is chosen.
When you recreated the table, the planner may have chosen a different plan
because the stats were different, and hence you did not see the issue
again. If you can reproduce the problem, it will be a good idea to run
EXPLAIN to see the query plan.

Thanks,
Pavan

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

[Pavel Stehule]

2012/11/21 Greg Sabino Mullane :
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
> Gavin Flower asks:
>
>> Would it be appropriate to make it a WARNING in 9.2.2, then
>> increase the length in 9.3?
>
> No: revisions are reserved for bug fixes. This would be more of
> a behavior fix and as such would go into a major version.
>
> Gavan Schneider wrote:
>> (Wild speculation) There may be a "sweet spot" using even shorter
>> identifiers than is the case now, with full disambiguation, which
>> might improve overall performance.
>
> I really don't think the length is really a bottleneck, but others
> can correct me if it is.
>
> Tom Lane wrote:
>> There's some possible value in having a non-default option to throw
>> error for overlength names, but TBH I fear that it won't buy all that
>> much, because people won't think to turn it on when testing.
>>
>> Given the historical volume of complaints (to wit, none up to now),
>> I can't get very excited about changing the behavior here.  I think
>> we're more likely to annoy users than accomplish anything useful.
>
> Well, as with many other things, a lack of complaints does not indicate
> there is no problem. I've certainly seen this problem in the wild before,
> but have not bothered to file an official bug report or anything. Perhaps
> my bad, but the problem is out there. How would you feel about switching
> from NOTICE to WARNING, Tom? That seems to make a lot more sense as we
> are changing the user's input, which warrants more than a notice IMO.
>
> Separately, what are the objections to raising the size limit to 128?

significantly larger catalog

Pavel

>
> - --
> Greg Sabino Mullane g...@turnstep.com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201211211525
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -BEGIN PGP SIGNATURE-
>
> iEYEAREDAAYFAlCtOYMACgkQvJuQZxSWSsjmEQCfb6GOEs7jwst1ao70L+j8IW5q
> gNYAn110QAhwjuhUSW3/uexvU+StsfZz
> =iw6q
> -END PGP SIGNATURE-
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Ranjeet Dhumal]

Hi Tom ,

Sorry but i didn't understand that If this is a bug from  postgres version
then how the same query will be worked if i recreated the tables and with
same version of postgres.


On 21 November 2012 19:53, Tom Lane  wrote:

> Alban Hertroys  writes:
> >> Ranjeet Dhumal wrote:
> > records , am using 9.0.1 version of postgres.
>
> > This is quite possibly your problem: You're 9 bugfix releases behind
> > on a .0 release. You should be at 9.0.10 at least.
>
> Yes.  I seem to recall fixing a bug with exactly this symptom.
> Please update first, then if the problem is still there you can work
> on producing a self-contained test case.
>
> regards, tom lane
>



-- 
--Regards
  Ranjeet  R. Dhumal

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

Gavin Flower asks:

> Would it be appropriate to make it a WARNING in 9.2.2, then 
> increase the length in 9.3?

No: revisions are reserved for bug fixes. This would be more of 
a behavior fix and as such would go into a major version.

Gavan Schneider wrote:
> (Wild speculation) There may be a "sweet spot" using even shorter
> identifiers than is the case now, with full disambiguation, which
> might improve overall performance.

I really don't think the length is really a bottleneck, but others 
can correct me if it is.

Tom Lane wrote:
> There's some possible value in having a non-default option to throw
> error for overlength names, but TBH I fear that it won't buy all that
> much, because people won't think to turn it on when testing.
>
> Given the historical volume of complaints (to wit, none up to now),
> I can't get very excited about changing the behavior here.  I think
> we're more likely to annoy users than accomplish anything useful.

Well, as with many other things, a lack of complaints does not indicate 
there is no problem. I've certainly seen this problem in the wild before, 
but have not bothered to file an official bug report or anything. Perhaps 
my bad, but the problem is out there. How would you feel about switching 
from NOTICE to WARNING, Tom? That seems to make a lot more sense as we 
are changing the user's input, which warrants more than a notice IMO.

Separately, what are the objections to raising the size limit to 128?

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201211211525
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlCtOYMACgkQvJuQZxSWSsjmEQCfb6GOEs7jwst1ao70L+j8IW5q
gNYAn110QAhwjuhUSW3/uexvU+StsfZz
=iw6q
-END PGP SIGNATURE-




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

[GENERAL] Postgresql on Windows 8

[Heine Ferreira]

Hi

Do you have any plans to put the Windows version of Postgresql on
the Windows 8 app store? Also for PGAdmin and database drivers?
Aparantly there are major issues with sideloading in windows 8.

Thanks

H.F.

Re: [GENERAL] High SYS CPU - need advise

[Jeff Janes]

On Wed, Nov 21, 2012 at 7:29 AM, Vlad Marchenko  wrote:

> update on my problem: despite pgbouncer, the problem still occures on my
> end.

As Merlin asked, how big is the pool?  Maybe you are using a large
enough pool so as to defeat the purpose of restricting the number of
connections.


> Also, interesting observation - I ran several tests with pgbench, using
> queries that I think are prone to trigger high-sys-cpu-stall. What I noticed
> is when pgbench is started with prepared mode, the system behaves fine
> during stress-test (high user cpu - 85-90%, low sys cpu - 5-7%), high TPS.
> Though when I used non-prepared modes, the sys cpu portion jumps to 40% (and
> tps drops dramatically as well, but this is understandable).  The test
> queries are pretty long (10kb+), with couple of outer joins across
> 1000-record tables with indexes.

Could you sanitize the queries (and some statements to generate dummy
data) enough to share?

>
> Maybe, we are looking in a wrong place and the issue is somewhere within
> planer/parser? Is there some extensive locking used in there?

I don't think the locking is particular extensive, but it could be
enough extra to drive something over the edge.

But it would be the same nature of locking as elsewhere (spinlocks and
lwlocks), so it doesn't really change the nature of the problem, which
is still "Why do these user-space locks turn into high SYS cpu?"

> Another observation - it's harder to trigger high-sys-cpu stall on a freshly
> restarted postgresql. Though if it was running for a while, then it's much
> easier to do.

Maybe the long running time has built up enough resource usage to
cause the kernel scheduler to get into a snit, so it decides to
preempt the process while it holds a spinlock, and then refuses to run
it again for a while.

Cheers,

Jeff


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

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Wed, Nov 21, 2012 at 9:29 AM, Vlad Marchenko  wrote:
> update on my problem: despite pgbouncer, the problem still occures on my
> end.
>
> Also, interesting observation - I ran several tests with pgbench, using
> queries that I think are prone to trigger high-sys-cpu-stall. What I noticed
> is when pgbench is started with prepared mode, the system behaves fine
> during stress-test (high user cpu - 85-90%, low sys cpu - 5-7%), high TPS.
> Though when I used non-prepared modes, the sys cpu portion jumps to 40% (and
> tps drops dramatically as well, but this is understandable).  The test
> queries are pretty long (10kb+), with couple of outer joins across
> 1000-record tables with indexes.
>
> Maybe, we are looking in a wrong place and the issue is somewhere within
> planer/parser? Is there some extensive locking used in there?
>
> Another observation - it's harder to trigger high-sys-cpu stall on a freshly
> restarted postgresql. Though if it was running for a while, then it's much
> easier to do.

what pgbouncer mode, and how large is your pool.

merlin


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

Re: [GENERAL] High SYS CPU - need advise

[Vlad Marchenko]

update on my problem: despite pgbouncer, the problem still occures on my 
end.


Also, interesting observation - I ran several tests with pgbench, using 
queries that I think are prone to trigger high-sys-cpu-stall. What I 
noticed is when pgbench is started with prepared mode, the system 
behaves fine during stress-test (high user cpu - 85-90%, low sys cpu - 
5-7%), high TPS. Though when I used non-prepared modes, the sys cpu 
portion jumps to 40% (and tps drops dramatically as well, but this is 
understandable).  The test queries are pretty long (10kb+), with couple 
of outer joins across 1000-record tables with indexes.


Maybe, we are looking in a wrong place and the issue is somewhere within 
planer/parser? Is there some extensive locking used in there?


Another observation - it's harder to trigger high-sys-cpu stall on a 
freshly restarted postgresql. Though if it was running for a while, then 
it's much easier to do.


-- vlad

On 11/19/12 8:33 AM, Merlin Moncure wrote:

On Sun, Nov 18, 2012 at 4:24 PM, Jeff Janes  wrote:

On Fri, Nov 16, 2012 at 12:13 PM, Vlad  wrote:

ok, I've applied that patch and ran. The stall started around 13:50:45...50
and lasted until the end

https://dl.dropbox.com/u/109778/postgresql-2012-11-16_134904-stripped.log

That isn't as much log as I expected.  But I guess only the tip of the
iceberg gets logged, it could be that most spinlocks are contended but
then get acquired just before the sleep (and log) would have happened.

I'm not sure how to figure out just how much spinning is going on
below the surface, but my gut feeling is there isn't enough of it to
explain the amount of slowdown you are seeing (and it would probably
be accounted for as user time rather than system time)


yes. classic spinlock contention symptoms are high user cpu and almost
work getting done.   something else is going on here?


Maybe we could whip up a dummy standalone  program that uses PG's
spinlock code in a intentionally contended  way and see how your
system reports resource usage for that.  Maybe your system reports
fighting for cache lines or whatever is going on behind the scenes as
system time rather than user time, or something.

I don't think this is the case.  It's looking more and more like
scheduler issues, although it's unclear if that is due to some kind of
regression or just symptoms of backends piling up.  I'm also starting
to wonder if the spinlock contention we are seeing (which according to
your patch really isn't all that much really) is just symptomatic of
scheduler issues.


It would be a little easier to figure out what was going on there if
the log included the pid (%p) so we can see if it is the same process
doing a bunch of consecutive sleeps, or a bunch of different
processes.  Also, logging the millisecond (%m rather than %t) would be
helpful.

But the way that the point of contention jumps around a lot points to
a scheduler issue rather than a postgres-specific issue.  A backend
gets pre-empted while holding a spinlock, and then the kernel is
resistant to rescheduling it even though all other processes are piled
up behind it.  But again, that theory would lead to more log lines
than we see, I would think.

yeah.  I asked for some longer strace logs but then everyone went home
for the weekend.


Anyway, pgbouncer might help by ensuring that a connection gets
pre-empted by pgbouncer at a point where it has no locks, rather than
by the kernel at a point where it does hold locks.

yes -- I think pgbouncer is the key here because it's going to tell us
if the scheduler is the culprit here.  If you look carefully at the
strace logs, you'll see that system call times become unpredictable as
they are getting scheduled out...particularly yielding calls like
select().  So we need to determine if this is normal high load
behavior or if there is an underlying mechanic.


Any chance you could try an older kernel and see if that reduces the problem?

This is one thing to try.  Personally I think pgbouncer/transaction
mode is low hanging fruit if it 'drops in' to OP's app. If problem
goes away this will pretty much point the finger at Linux AFAICT (in
which case we can start messing around with the kernel).

merlin




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

[GENERAL] Postgres Security Audit/Checklist

[Granile, Gonzalo A]

Hi all,

 

We are currently doing a security assessment over a PostgreSQL database
sitting on a Linux server. Just wondering if anyone has a script and/or
audit program for gathering information and assessing a postgreSQL
database (similar to the oracle sql scripts that have been distributed
previously).

 

Any help is much appreciated

 

Regards

 

Gonzalo Granile
Sr. Consultant
Forensic Technology Services

KPMG
Buenos Aires, Argentina
Tel: (+54) 11.4316.5700 int 4389
www.kpmg.com.ar  

P Antes de imprimir piense en su compromiso con el Medio Ambiente.

 

 

 


***
The information in this e-mail is confidential and may be legally privileged. 
It is intended solely for the addressee. Access to this e-mail by anyone else 
is unauthorized. If you have received this communication in error, please 
address with the subject heading "Received in error," send to the original 
sender , then delete the e-mail and destroy any copies of it. If you are not 
the intended recipient, any disclosure, copying, distribution or any action 
taken or omitted to be taken in reliance on it, is prohibited and may be 
unlawful. Any opinions or advice contained in this e-mail are subject to the 
terms and conditions expressed in the governing KPMG client engagement letter. 
Opinions, conclusions and other information in this e-mail and any attachments 
that do not relate to the official business of the firm are neither given nor 
endorsed by it.

KPMG cannot guarantee that e-mail communications are secure or error-free, as 
information could be intercepted, corrupted, amended, lost, destroyed, arrive 
late or incomplete, or contain viruses. 

This email is being sent out by KPMG International Cooperative (“KPMG 
International”) on behalf of the local KPMG member firm providing services to 
you.  KPMG International Cooperative ("KPMG International") is a Swiss entity 
that serves as a coordinating entity for a network of independent firms 
operating under the KPMG name. KPMG International Cooperative (“KPMG 
International”) provides no services to clients. Each member firm of KPMG 
International Cooperative (“KPMG International”) is a legally distinct and 
separate entity and each describes itself as such.  Information about the 
structure and jurisdiction of your local KPMG member firm can be obtained from 
your KPMG representative.

This footnote also confirms that this e-mail message has been swept by 
AntiVirus software. . 
***

<>

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Tom Lane]

Alban Hertroys  writes:
>> Ranjeet Dhumal wrote:
> records , am using 9.0.1 version of postgres.

> This is quite possibly your problem: You're 9 bugfix releases behind
> on a .0 release. You should be at 9.0.10 at least.

Yes.  I seem to recall fixing a bug with exactly this symptom.
Please update first, then if the problem is still there you can work
on producing a self-contained test case.

regards, tom lane


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

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Alban Hertroys]

> Ranjeet Dhumal wrote:
>> records , am using 9.0.1 version of postgres.

This is quite possibly your problem: You're 9 bugfix releases behind
on a .0 release. You should be at 9.0.10 at least.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


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

Re: [GENERAL] output inserted

[David Johnston]

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Peter Kroon
Sent: Wednesday, November 21, 2012 8:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] output inserted

 

How to I output the insert in PostgreSQL?

 

DROP TABLE IF EXISTS a_001;

CREATE TEMP TABLE a_001(

  vl text

);

DROP TABLE IF EXISTS a_002;

CREATE TEMP TABLE a_002(

  vl text

);

 

INSERT INTO a_001

OUTPUT INSERTED.* INTO a_002 --mssql

SELECT 'text for insertion';

 

SELECT vl FROM a_002;

 

 

My best guess, since I can only infer what that particular syntax means, is
that you have to put an AFTER INSERT TRIGGER on table a_001.

 

David J.

 

 

Re: [GENERAL] output inserted

[Ryan Kelly]

On Wed, Nov 21, 2012 at 02:41:24PM +0100, Peter Kroon wrote:
> How to I output the insert in PostgreSQL?
> 
> DROP TABLE IF EXISTS a_001;
> CREATE TEMP TABLE a_001(
> vl text
> );
> DROP TABLE IF EXISTS a_002;
> CREATE TEMP TABLE a_002(
> vl text
> );
> 
> INSERT INTO a_001
> OUTPUT INSERTED.* INTO a_002 --mssql
> SELECT 'text for insertion';
> 
> SELECT vl FROM a_002;

WITH data AS (
INSERT INTO a_001
SELECT 'text for insertion'
RETURNING *
)
INSERT INTO a_002
SELECT * FROM data;

This is one possible solution.

-Ryan P. Kelly


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

Re: [GENERAL] Escaping regexp special characters in field value

[David Johnston]

ILIKE is probably better - without an escape:

 

WHERE field1 ILIKE field2 ESCAPE ''

 

You could also try: 

 

WHERE upper(field1) = upper(field2)

 

David J.

 

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau
Sent: Wednesday, November 21, 2012 4:39 AM
To: Postgres General Postgres General
Subject: [GENERAL] Escaping regexp special characters in field value

 

I do have a field for which I want to perform a join with some other field
in another table using case without case sensitivity perhaps using ~*.

One of this fields may contain + characters or unbalanced parenthesis. Is
there a way or some function that may mask out the regexp awareness of any
of these characters if they appear the field values.

 

Should I need to write a function to escape each of these characters by
placing a "\" character before it?

 

Allan.

[GENERAL] output inserted

[Peter Kroon]

How to I output the insert in PostgreSQL?

DROP TABLE IF EXISTS a_001;
CREATE TEMP TABLE a_001(
vl text
);
DROP TABLE IF EXISTS a_002;
CREATE TEMP TABLE a_002(
vl text
);

INSERT INTO a_001
OUTPUT INSERTED.* INTO a_002 --mssql
SELECT 'text for insertion';

SELECT vl FROM a_002;

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Albe Laurenz]

Ranjeet Dhumal wrote:
> I got this error on two different tables , first one is empty and
second one has near about 60k
> records , am using 9.0.1 version of postgres.
> And when i dropped that both table and recreated with same data and
structure  query ran successfully
> , am not able to understand how this is happening suddenly because for
same table and everything  is
> working fine  without error .

Try to find out under what condition the problem occurs.
A reproducible test case would be most helpful.

Yours,
Laurenz Albe


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

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Ranjeet Dhumal]

Hi Laurenz ,

I got this error on two different tables , first one is empty and second
one has near about 60k records , am using 9.0.1 version of postgres.
And when i dropped that both table and recreated with same data and
structure  query ran successfully , am not able to understand how this is
happening suddenly because for same table and everything  is working fine
 without error .


On 21 November 2012 18:14, Albe Laurenz  wrote:

> Ranjeet Dhumal wrote:
> > When am trying to query a table temp_table1(sms_type
> varchar(20),sms_info varchar(100),sms_id integer)
> > Query :: select sms_type,count(*) from  temp_table1 group by 1 order
> by 2 desc;
> > Then i got following errors , i dont know whats wrong in this .
> > ERROR: volatile EquivalenceClass has no sortref
> >
> > This is my first post to forum , Please suggest me if i miss something
> .
>
> Do you get this error regardless of what is
> in the table?  I tried on 9.2 with an empty table
> and with one with a few random values and got no
> error.
>
> It would be helpful to know which PostgreSQL version
> you have.
> Can you construct a reproducible test case?
>
> Yours,
> Laurenz Albe
>



-- 
--Regards
  Ranjeet  R. Dhumal

Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Albe Laurenz]

Ranjeet Dhumal wrote:
> When am trying to query a table temp_table1(sms_type
varchar(20),sms_info varchar(100),sms_id integer)
> Query :: select sms_type,count(*) from  temp_table1 group by 1 order
by 2 desc;
> Then i got following errors , i dont know whats wrong in this .
> ERROR: volatile EquivalenceClass has no sortref
> 
> This is my first post to forum , Please suggest me if i miss something
.

Do you get this error regardless of what is
in the table?  I tried on 9.2 with an empty table
and with one with a few random values and got no
error.

It would be helpful to know which PostgreSQL version
you have.
Can you construct a reproducible test case?

Yours,
Laurenz Albe


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

Re: [GENERAL] declare variable in udf

[Raymond O'Donnell]

On 21/11/2012 12:01, Peter Kroon wrote:
> So, multiple DECLARE sections are not allowed?

Actually, I just had a quick look here -

  http://www.postgresql.org/docs/9.2/static/plpgsql-structure.html

- and it seems that you can have multiple declare - begin - end blocks.

Try it and see!

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] declare variable in udf

[Pavel Stehule]

2012/11/21 Peter Kroon :
> So, multiple DECLARE sections are not allowed?

it  is not allowed in plpgsql

resp. DECLARE is related to block - if you use nested block, then you
can use nested DECLAREs

Regards

Pavel Stehule


>
>
> 2012/11/21 Raymond O'Donnell 
>>
>> On 21/11/2012 11:42, Peter Kroon wrote:
>> > Hello,
>> >
>> > How do I declare a variable after BEGIN?
>> > I want to declare it in the if statement.
>> >
>> > DROP FUNCTION IF EXISTS tmp_test(integer);
>> > CREATE FUNCTION tmp_test(
>> > p_id integer
>> > )
>> > RETURNS text
>> > AS $$
>> > DECLARE the_return_value text;
>> > BEGIN
>> > DROP TABLE IF EXISTS temp_test_table;
>> > CREATE TEMP TABLE temp_test_table(
>> > some_value text
>> > );
>> > INSERT INTO temp_test_table
>> > SELECT data FROM table WHERE id=p_id;
>> > SELECT INTO the_return_value some_value FROM temp_test_table;
>> >
>> > IF 1=1 THEN
>> > --how do I declare a variable here? it this possible?
>> > RAISE NOTICE 'this is a notice';
>> > END IF;
>> >
>> > RETURN the_return_value;
>> >
>> > END;
>> > $$ language plpgsql
>>
>> I don't think that is possible - you have to declare all your variables
>> in the DECLARE section.
>>
>> Ray.
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> r...@iol.ie
>
>


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

Re: [GENERAL] declare variable in udf

[Peter Kroon]

So, multiple DECLARE sections are not allowed?


2012/11/21 Raymond O'Donnell 

> On 21/11/2012 11:42, Peter Kroon wrote:
> > Hello,
> >
> > How do I declare a variable after BEGIN?
> > I want to declare it in the if statement.
> >
> > DROP FUNCTION IF EXISTS tmp_test(integer);
> > CREATE FUNCTION tmp_test(
> > p_id integer
> > )
> > RETURNS text
> > AS $$
> > DECLARE the_return_value text;
> > BEGIN
> > DROP TABLE IF EXISTS temp_test_table;
> > CREATE TEMP TABLE temp_test_table(
> > some_value text
> > );
> > INSERT INTO temp_test_table
> > SELECT data FROM table WHERE id=p_id;
> > SELECT INTO the_return_value some_value FROM temp_test_table;
> >
> > IF 1=1 THEN
> > --how do I declare a variable here? it this possible?
> > RAISE NOTICE 'this is a notice';
> > END IF;
> >
> > RETURN the_return_value;
> >
> > END;
> > $$ language plpgsql
>
> I don't think that is possible - you have to declare all your variables
> in the DECLARE section.
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>

Re: [GENERAL] declare variable in udf

[Raymond O'Donnell]

On 21/11/2012 11:42, Peter Kroon wrote:
> Hello,
> 
> How do I declare a variable after BEGIN?
> I want to declare it in the if statement.
> 
> DROP FUNCTION IF EXISTS tmp_test(integer);
> CREATE FUNCTION tmp_test(
> p_id integer
> )
> RETURNS text
> AS $$
> DECLARE the_return_value text;
> BEGIN
> DROP TABLE IF EXISTS temp_test_table;
> CREATE TEMP TABLE temp_test_table(
> some_value text
> );
> INSERT INTO temp_test_table
> SELECT data FROM table WHERE id=p_id;
> SELECT INTO the_return_value some_value FROM temp_test_table;
> 
> IF 1=1 THEN
> --how do I declare a variable here? it this possible?
> RAISE NOTICE 'this is a notice';
> END IF;
> 
> RETURN the_return_value;
> 
> END;
> $$ language plpgsql

I don't think that is possible - you have to declare all your variables
in the DECLARE section.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

[GENERAL] declare variable in udf

[Peter Kroon]

Hello,

How do I declare a variable after BEGIN?
I want to declare it in the if statement.

DROP FUNCTION IF EXISTS tmp_test(integer);
CREATE FUNCTION tmp_test(
p_id integer
)
RETURNS text
AS $$
DECLARE the_return_value text;
BEGIN
DROP TABLE IF EXISTS temp_test_table;
CREATE TEMP TABLE temp_test_table(
some_value text
);
INSERT INTO temp_test_table
SELECT data FROM table WHERE id=p_id;
 SELECT INTO the_return_value some_value FROM temp_test_table;

IF 1=1 THEN
--how do I declare a variable here? it this possible?
RAISE NOTICE 'this is a notice';
END IF;

RETURN the_return_value;

END;
$$ language plpgsql


Best,
Peter

[GENERAL] ERROR: volatile EquivalenceClass has no sortref

[Ranjeet Dhumal]

Hi All ,

When am trying to query a table temp_table1(sms_type varchar(20),sms_info
varchar(100),sms_id integer)
Query :: select sms_type,count(*) from  temp_table1 group by 1 order by 2
desc;
Then i got following errors , i dont know whats wrong in this .
*ERROR: volatile EquivalenceClass has no sortref*

This is my first post to forum , Please suggest me if i miss something .

-- 
--Regards
  Ranjeet  R. Dhumal

Re: [GENERAL] PostgreSQL training recommendations?

[Chris Travers]

On Wed, Nov 21, 2012 at 1:49 AM, Jasen Betts  wrote:

> On 2012-10-17, Vincent Veyron  wrote:
> >
> > I am surprised none of the fine contributors to this thread mentionned
> > an activity they practice extensively, which is reading this list's
> > content every day.
> >
> > Best training material ever in my opinion.
>
> Yeah, if you want to learn PostgreSQL this list, the "sql" list, and
> the "novice" list will provide both answers and example problems.
>

I would add I have learned a tremendous amount from the performance list as
well.

But beyond that just participating in the discussions here one learns a
lot, same with reading Bruce's presentations and other good documentation.
 That doesn't mean it is always easy to fit pieces together but it takes
time.

I have also occasionally had important "aha!" moments reading
planet.postgresql.org also.

Part of the issue of course is that performance tuning often requires a
decent understanding of lower-level aspects to what the database is
actually doing.  The database goes to great efforts to be fast and the
question is always what it is doing that you can help speed up.

Best Wishes,
Chris Travers

Re: [GENERAL] COPY FROM in psql

[Matthew Vernon]

t...@sss.pgh.pa.us (Tom Lane) writes:

> Matthew Vernon  writes:
>> naiively, you might try:
>> \set pwd '\'' `pwd` '\''
>> COPY table FROM :pwd || '/relative/path/to/data' ;
>
> Umm ... why don't you just use a relative path as-is, with \copy
> instead of COPY?

Thanks for the suggestion, but I was avoiding \copy because the
quantities of data involved are large, and the documentation suggests
that COPY is better than \copy for large data volumes.

Thanks,

Matthew

-- 
Matthew Vernon
Quantitative Veterinary Epidemiologist
Epidemiology Research Unit, SAC Inverness


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

Re: [GENERAL] PostgreSQL training recommendations?

[Jasen Betts]

On 2012-10-17, Vincent Veyron  wrote:
>
> I am surprised none of the fine contributors to this thread mentionned
> an activity they practice extensively, which is reading this list's
> content every day.
>
> Best training material ever in my opinion.

Yeah, if you want to learn PostgreSQL this list, the "sql" list, and
the "novice" list will provide both answers and example problems.

-- 
⚂⚃ 100% natural



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

Re: [GENERAL] PostgresQL intallation error

[Jasen Betts]

On 2012-10-27, Raul Feliu  wrote:
> --_4c4db745-219d-4817-8789-6e7997227fee_
> Content-Type: text/plain; charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
>
> I have windows vista. I tried to run the installer in admin mode and I disa=
> bled UAC. Still having the same problem.

rightclick 
"run as administrator" ?

-- 
⚂⚃ 100% natural



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

[GENERAL] Escaping regexp special characters in field value

[Allan Kamau]

I do have a field for which I want to perform a join with some other field
in another table using case without case sensitivity perhaps using ~*.
One of this fields may contain + characters or unbalanced parenthesis. Is
there a way or some function that may mask out the regexp awareness of any
of these characters if they appear the field values.

Should I need to write a function to escape each of these characters by
placing a "\" character before it?

Allan.

Re: [GENERAL] Postgresql - 8.3 Replication in windows

[Jasen Betts]

On 2012-11-02, dinesh kumar  wrote:
> --20cf3071cc56678d5104cd82409f
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi ,
>
> In windows we do not have SCP/RSYNC utility commands, which helps us to
> send the archives to remote/slave server.

pscp (putty.org)
deltacopy 

-- 
⚂⚃ 100% natural



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

Re: [GENERAL] Performance Testing Metrics

[Vincent Veyron]

Le mardi 20 novembre 2012 à 03:54 -0800, Harry a écrit :
> Hello All,
> 
> I need help to know how to get below things in Postgresql :-
> 1) No. of active connections?
> 2) No. of non-responding connections?
> 3) Queries which are running beyond time(i.e. time which 'll set in database
> query)?
> 5) Dead Locked Queries?
> 4) Process ID's of dead-locked queries?
> 5) No. of queries in non-responding state?
> 
> 

Most of this is explained here :

http://www.postgresql.org/docs/9.2/static/monitoring.html


> 
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Performance-Testing-Metrics-tp5732897.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des assurances sinistres et des dossiers contentieux pour 
le service juridique



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

Re: [GENERAL] PG under OpenVZ?

[Frank Lanitz]

Am 2012-11-13 14:53, schrieb François Beausoleil:

Hi!

I've found an old thread on OpenVZ:

(2008): 
http://archives.postgresql.org/pgsql-performance/2008-03/msg00076.php


And a more recent question that scared me a bit:

(2011): 
http://serverfault.com/questions/281783/running-mongodb-with-openvz


On the PostgreSQL general mailing list, I've only found 54 results
when searching for OpenVZ. I'm wondering if OpenVZ is simply
unpopular, or not used at all for PG. What experiences do you have
with OpenVZ? Any performance problems?

We're buying bare metal to run our clusters on, and the supplier is
late delivering the machines. They suggested lending us a machine and
run PostgreSQL under OpenVZ. When the real hardware is ready, we'd
migrate the VZ over to the new physical servers. Thoughts on this?

I have no experience with OpenVZ itself, so if you have general
comments about it's stability and/or performance, even unrelated to
PostgreSQL, I'd appreciate.


Running a small PG-Server for private purposes on openVZ. Cannot 
complain so far.


Cheers,
Frank



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

Re: [GENERAL] PG under OpenVZ?

[Jasen Betts]

On 2012-11-13, François Beausoleil  wrote:
> Hi!
>
> I've found an old thread on OpenVZ:
>
> (2008): http://archives.postgresql.org/pgsql-performance/2008-03/msg00076.php
>
> And a more recent question that scared me a bit:
>
> (2011): http://serverfault.com/questions/281783/running-mongodb-with-openvz
>
> On the PostgreSQL general mailing list, I've only found 54 results when 
> searching for OpenVZ. I'm wondering if OpenVZ is simply unpopular, or not 
> used at all for PG. What experiences do you have with OpenVZ? Any performance 
> problems?
>
> We're buying bare metal to run our clusters on, and the supplier is late 
> delivering the machines. They suggested lending us a machine and run 
> PostgreSQL under OpenVZ. When the real hardware is ready, we'd migrate the VZ 
> over to the new physical servers. Thoughts on this?
>
> I have no experience with OpenVZ itself, so if you have general
> comments about it's stability and/or performance, even unrelated to
> PostgreSQL, I'd appreciate.   

We run several database clusters under openvz. I have nothing to report.

openvz is pseudo-virtualisation.  All the processes run under the 
host kernal and appear in the proc-table, and use the host systems 
filesystem and device drivers. they just see different filesystems 
and resource limits according to their vz.

this basically means running the clusters under openvz is not
radically different from running them all on the host system 


-- 
⚂⚃ 100% natural



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