Re: [GENERAL] not aborting transactions on failed select

[Sergey Shelukhin]

The query suffers from the auto-lower-casing of unquoted table names, which
is not ANSI compliant. Technically we could add quotes (and stay ANSI), but
then MySQL would break without explicitly setting it to use ANSI mode, so
it's a lose-lose situation if we do not want to have DB-specific code.
So, the fix to the query exists in this case, and the failure will be
addressed, however in general it's important that this path stays as perf
optimization only, with things working without it (we do have tests that
verify that it returns the same results as ORM when it works). I guess I'll
have to work around it... Thanks.


On Tue, Sep 10, 2013 at 9:39 PM, Scott Marlowe wrote:

> On Tue, Sep 10, 2013 at 7:02 PM, Sergey Shelukhin
>  wrote:
> > Hi.
> > Is there any way to make postgres not abort the transaction on failed
> > select?
> >
> > I have a system that uses ORM to retrieve data; ORM is very slow for some
> > cases, so there's a perf optimization that issues ANSI SQL queries
> directly
> > thru ORM's built-in passthru, and falls back to ORM if they fail.
> > All of these queries are select-s, and the retrieval can be a part of an
> > external transaction.
> >
> > It worked great in MySQL, but Postgres being differently
> ANSI-non-compliant,
> > the queries do fail. Regardless of whether they can be fixed, in such
> cases
> > the fall-back should work. What happens in Postgres however is that the
> > transaction is aborted; all further SELECTs are ignored.
>
> I would like to see the query and in what way PostgreSQL is failing.
> Generally a query that works on mysql and fails on postgresql is
> itself usually non-ansi compliant.
>
> As others pointed out, you can use savepoints if you need to rollback
> part of a transaction to a previously good point and start back from
> there.
>

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Re: [GENERAL] pg_largeobjects

[John R Pierce]

On 9/10/2013 9:49 PM, James Sewell wrote:


As an aside, is there any reason to use pg_largeobjects if I am 
storing data under 1GB which doesn't require random reads any more? My 
impression is no?


one good reason to use LO is so you can read the data like its a 
file.me, I'd as soon use NFS or whatever for that, with a file path 
in the database, but I understand there's scenarios where thats not 
practical.


--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

Re: [GENERAL] Need help with Inet type

[Christoph Moench-Tegeder]

## Eric Lamer (eric.la...@intact.net):

>   I can do something like:
> 
>Select * From logs Where src_ip IN (Select ip from ip_table where zone 
> = 'ZONE_a');
> 
>Of course that does not work since it does not work with Inet type and 
> I cannot use << because I have more than 1 row return from the second 
> select.

How about:
   SELECT DISTINCT logs.* FROM logs
 JOIN ip_table ON logs.srcip << ip_table.ip
 WHERE zone = 'ZONE_a';

Regards,
Christoph

-- 
Spare Space


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

[GENERAL] pg_largeobjects

[James Sewell]

Hello all,

I have a table which makes use of pg_largeobjects. I am inserting rows into
the table as user1. If I connect to the database as user2 I can SELECT
data, but can not open the large object for reading (user1 can do this). I
don't want to set lo_compat_privileges as then user3 (who can't SELECT from
the services tables) would be able to read the large object.

I realise that pg_largeobjects is "partially obsolete" and I could solve
the problem by just using TOAST, but this still seems incorrect.

As an aside, is there any reason to use pg_largeobjects if I am storing
data under 1GB which doesn't require random reads any more? My impression
is no?

SELECT * from services where payload = '51414';
-[ RECORD 1 ]--+
id | 2263
insert_date| 2013-08-16 15:39:56.774
instance   | createApp4
payload| 51414
update_date| 2013-09-09 09:39:31.454
pe_service_transactions_id | 2262]

\lo_list
Large objects
-[ RECORD 1 ]
ID  | 51414
Owner   | user1
Description |

SELECT loread(lo_open(51414, 262144), 9);
ERROR:  permission denied for large object 51414


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P* (03) 8370 8000 * **W* www.lisasoft.com  *F*(03) 8370 8000

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] not aborting transactions on failed select

[Scott Marlowe]

On Tue, Sep 10, 2013 at 7:02 PM, Sergey Shelukhin
 wrote:
> Hi.
> Is there any way to make postgres not abort the transaction on failed
> select?
>
> I have a system that uses ORM to retrieve data; ORM is very slow for some
> cases, so there's a perf optimization that issues ANSI SQL queries directly
> thru ORM's built-in passthru, and falls back to ORM if they fail.
> All of these queries are select-s, and the retrieval can be a part of an
> external transaction.
>
> It worked great in MySQL, but Postgres being differently ANSI-non-compliant,
> the queries do fail. Regardless of whether they can be fixed, in such cases
> the fall-back should work. What happens in Postgres however is that the
> transaction is aborted; all further SELECTs are ignored.

I would like to see the query and in what way PostgreSQL is failing.
Generally a query that works on mysql and fails on postgresql is
itself usually non-ansi compliant.

As others pointed out, you can use savepoints if you need to rollback
part of a transaction to a previously good point and start back from
there.


-- 
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] not aborting transactions on failed select

[David Johnston]

Sergey Shelukhin wrote
> ORM in this case doesn't execute the failing statements, we do. And
> obviously we want to avoid implementing another "better ORM"
> w/database-specific code for this "side path" if possible, so we just
> stick
> to ANSI SQL (for now).
> 
> As for the question itself, I believe the relevant standard (that is
> SQL92)
> is:
>  "The execution of a 
> 
>  may be initiated
> implicitly
>  by an implementation when it detects unrecoverable errors. When
>  such an error occurs, an exception condition is raised:
> transaction
>  rollback with an implementation-defined subclass code."
> 
> In no way is a select syntax failure unrecoverable error, although of
> course this section leaves a lot to interpretation...

PostgreSQL waits for an explicit Rollback in your situation - it just keeps
giving you exceptions if you choose not to do so.  But if the system were to
crash it is perfectly in its rights to forgo waiting for you to Rollback and
instead do so itself - thus implicitly.  Note that this is required so that
savepoint behavior can work properly.

I have and can argue for the behavior you describe but the hurdle to
implement it is very high.  I would want some form of "approve error"
command that could be sent (manually in application code or automatically if
interactive) instead of blindly moving forward - MySQL behavior as you
describe is not desireable.  In any case syntax errors in application code
are bad but I can see the use for it in interactive mode - but a manual
"approve error" command makes dealing with this distinction somewhat
flexible if you read error causes and are conservative in code when you
approve the error.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/not-aborting-transactions-on-failed-select-tp5770387p5770400.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] not aborting transactions on failed select

[Sergey Shelukhin]

ORM in this case doesn't execute the failing statements, we do. And
obviously we want to avoid implementing another "better ORM"
w/database-specific code for this "side path" if possible, so we just stick
to ANSI SQL (for now).

As for the question itself, I believe the relevant standard (that is SQL92)
is:
 "The execution of a  may be initiated
implicitly
 by an implementation when it detects unrecoverable errors. When
 such an error occurs, an exception condition is raised: transaction
 rollback with an implementation-defined subclass code."

In no way is a select syntax failure unrecoverable error, although of
course this section leaves a lot to interpretation...

There may be more but I didn't find it. One example of the spirit of the
standard may be the constraint check description:
"When a constraint is checked other than at the end of an SQL- transaction,
if it is not satisfied, then an exception condition is raised and the
SQL-statement that caused the constraint to be checked has no effect other
than entering the exception information into the diagnostics area. "

This is the behavior I would intuitively expect from select in this case
(which is not a constraint check failure, of course; but it's even less
"dangerous" to ignore, in spirit).



On Tue, Sep 10, 2013 at 7:03 PM, David Johnston  wrote:

> Sergey Shelukhin wrote
> > Hi.
> > Is there any way to make postgres not abort the transaction on failed
> > select?
> >
> > I have a system that uses ORM to retrieve data; ORM is very slow for some
> > cases, so there's a perf optimization that issues ANSI SQL queries
> > directly
> > thru ORM's built-in passthru, and falls back to ORM if they fail.
> > All of these queries are select-s, and the retrieval can be a part of an
> > external transaction.
> >
> > It worked great in MySQL, but Postgres being differently
> > ANSI-non-compliant, the queries do fail. Regardless of whether they can
> be
> > fixed, in such cases the fall-back should work. What happens in Postgres
> > however is that the transaction is aborted; all further SELECTs are
> > ignored.
> >
> > Is there some way to get around this and not abort the transaction on
> > failed selects?
> > This behavior seems extremely counter-intuitive.
>
> This behavior is extremely intuitive.  I have a transaction.  Either the
> whole things succeeds or the whole thing fails.  Not, "its OK if select
> statements fail; I'll just try something else instead."
>
> If the ORM knows its going to issue something that could fail AND it needs
> to do so within a transaction it needs to issue a SAVEPOINT, try the
> SELECT,
> then either release the savepoint (on success) or ROLLBACK_TO_SAVEPONT to
> revert to the savepoint (on failure) then continue on with its work.
>
> Short answer is that the PostgreSQL team has a made a decision to have
> transactions behave strictly according to their intended purpose and it is
> not possible to make them behave less-correctly even if you know that your
> application can compensate for degradation.
>
> I cannot speak about the MySQL experience and my cursory search of their
> documentation describing this behavior got me nothing.  I also cannot speak
> intelligently about the SQL standard but from experience and instinct the
> PostgreSQL behavior is what the standard intends and relying on the ability
> for a fail statement of any kind to not cause an open transaction to fail
> (in the absence of a savepoint) may have been a convenient choice but one
> that is non-standard and thus potentially (and in reality) non-portable.
>
> I could be mistaken on this - though I doubt - since I have not personally
> tried to accomplish this in PostgreSQL (though the default behavior is
> something I've experienced) and I cannot confirm or test any of this on a
> MySQL installation.  Others will correct my if I am indeed mistaken.
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/not-aborting-transactions-on-failed-select-tp5770387p5770393.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Re: [GENERAL] not aborting transactions on failed select

[David Johnston]

Sergey Shelukhin wrote
> Hi.
> Is there any way to make postgres not abort the transaction on failed
> select?
> 
> I have a system that uses ORM to retrieve data; ORM is very slow for some
> cases, so there's a perf optimization that issues ANSI SQL queries
> directly
> thru ORM's built-in passthru, and falls back to ORM if they fail.
> All of these queries are select-s, and the retrieval can be a part of an
> external transaction.
> 
> It worked great in MySQL, but Postgres being differently
> ANSI-non-compliant, the queries do fail. Regardless of whether they can be
> fixed, in such cases the fall-back should work. What happens in Postgres
> however is that the transaction is aborted; all further SELECTs are
> ignored.
> 
> Is there some way to get around this and not abort the transaction on
> failed selects?
> This behavior seems extremely counter-intuitive.

This behavior is extremely intuitive.  I have a transaction.  Either the
whole things succeeds or the whole thing fails.  Not, "its OK if select
statements fail; I'll just try something else instead."  

If the ORM knows its going to issue something that could fail AND it needs
to do so within a transaction it needs to issue a SAVEPOINT, try the SELECT,
then either release the savepoint (on success) or ROLLBACK_TO_SAVEPONT to
revert to the savepoint (on failure) then continue on with its work.

Short answer is that the PostgreSQL team has a made a decision to have
transactions behave strictly according to their intended purpose and it is
not possible to make them behave less-correctly even if you know that your
application can compensate for degradation.

I cannot speak about the MySQL experience and my cursory search of their
documentation describing this behavior got me nothing.  I also cannot speak
intelligently about the SQL standard but from experience and instinct the
PostgreSQL behavior is what the standard intends and relying on the ability
for a fail statement of any kind to not cause an open transaction to fail
(in the absence of a savepoint) may have been a convenient choice but one
that is non-standard and thus potentially (and in reality) non-portable.

I could be mistaken on this - though I doubt - since I have not personally
tried to accomplish this in PostgreSQL (though the default behavior is
something I've experienced) and I cannot confirm or test any of this on a
MySQL installation.  Others will correct my if I am indeed mistaken.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/not-aborting-transactions-on-failed-select-tp5770387p5770393.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] not aborting transactions on failed select

[Sergey Shelukhin]

Hi.
Is there any way to make postgres not abort the transaction on failed
select?

I have a system that uses ORM to retrieve data; ORM is very slow for some
cases, so there's a perf optimization that issues ANSI SQL queries directly
thru ORM's built-in passthru, and falls back to ORM if they fail.
All of these queries are select-s, and the retrieval can be a part of an
external transaction.

It worked great in MySQL, but Postgres being differently
ANSI-non-compliant, the queries do fail. Regardless of whether they can be
fixed, in such cases the fall-back should work. What happens in Postgres
however is that the transaction is aborted; all further SELECTs are ignored.

Is there some way to get around this and not abort the transaction on
failed selects?
This behavior seems extremely counter-intuitive.

Thanks.

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Adrian Klaver]

On 09/10/2013 10:37 AM, Chris Curvey wrote:



Another development (possibly unrelated):  I tried **dumping** with
–no-privileges –no-tablespace –no-owner, and the restore went fine.



Probably has to do with whether you are dumping plain text or custom format:

http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html

-O
--no-owner
Do not output commands to set ownership of objects to match the original 
database. By default, pg_dump issues ALTER OWNER or SET SESSION 
AUTHORIZATION statements to set ownership of created database objects. 
These statements will fail when the script is run unless it is started 
by a superuser (or the same user that owns all of the objects in the 
script). To make a script that can be restored by any user, but will 
give that user ownership of all the objects, specify -O.


This option is only meaningful for the plain-text format. For the 
archive formats, you can specify the option when you call pg_restore.




--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] Call for design: PostgreSQL mugs

[Kevin Grittner]

patrick keshishian  wrote:
> One more "cute" idea that came to me last night. Here is a very

> poor attempt at it by yours truly; keep in mind I'm not a graphics
> artist. This image is for illustration purposes only!
> 
>     http://sidster.com/gallery/2013/09/10/elephant_paw.sml.jpg
> 
> Can you picture a bunch of these on a meeting table? If that image
> does not scream "Stampede!", I don't know what does. =)
> 
> Again, a great conversation starter.

I think I like this idea best so far!

Some supporting documentation for a final version:

http://www.asknature.org/strategy/29c12a353dab52ad8d4eb5d4337cefb9


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



-- 
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] Call for design: PostgreSQL mugs

[patrick keshishian]

One more "cute" idea that came to me last night. Here is a very
poor attempt at it by yours truly; keep in mind I'm not a graphics
artist. This image is for illustration purposes only!

http://sidster.com/gallery/2013/09/10/elephant_paw.sml.jpg

Can you picture a bunch of these on a meeting table? If that image
does not scream "Stampede!", I don't know what does. =)

Again, a great conversation starter.

Cheers,
--patrick

p.s., Although, I for one do not condone this, those who are bent on
bashing the competitor product, can simply put a sticker-image of a
certain other mammal under this cup! ;) a DIY mod if you will.


On 9/9/13, patrick keshishian  wrote:
> On 9/3/13, Andreas 'ads' Scherbaum  wrote:
>>
>> PostgreSQL folks!
>>
>> We are looking for the next big thing. Actually, it's a bit smaller: a
>> new design for mugs. So far we had big blue elephants, small blue
>> elephants, frosty elephants, white SQL code on black mugs ... Now it's
>> time to design something new.
>>
>>
>> What's in for you? Fame, of course - we will announce the designer of
>> the next mug along with the mugs itself. Plus 4 mugs for you and your
>> friends.
>>
>>
>> Do you have a cool idea? Please let us know. Either reply here or send
>> an email to pgeu-bo...@postgresql.org.
>
> One more idea, which is on the "cute"/lighter side. This idea
> can be reversed/is reversible, so you get two ideas i one:
>
> - Outer face of the mug just simple words "PostgreSQL"
>   optionally with a URL to official web-site.
> - On the inside bottom of the mug face of a cute elephant looking
>   at you with a smile.
> - On the outside bottom of the mug, the rear end of the elephant
>   with tail and all.
>
> You can reverse the bottom images and get a second design, where
> the inside bottom of the mug is the rear of the elephant with tail and
> all, and the outside bottom of the mug is the smiling elephant :-)
>
> If printing inside the mug is too difficult, cost prohibitive, then stick
> with the smiling elephant at the outside bottom of the mug.
>
> This type of a design would be a good ice-breaker/conversation
> starter in office meetings, etc.
>
> --patrick
>


-- 
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] invalid frontend message type 136

[David Johnston]

Steve Crawford wrote
> Sorry, I should have included that. The error occurred when an older 
> client running 8.3.7 (I know, ferreting and finishing upgrades on 
> clients with old libraries is in progress) on CentOS 5.3 (32-bit). Of 
> all the machines connecting to the server, this one pretty lightly 
> loaded and typically creates a new connection to the server somewhat 
> over 10,000 times/week. This is the first instance of the error.
> 
> These machines/connections are buried behind firewalls in an automated 
> system and this particular connection is password-less.
> 
> The server is PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by 
> gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit.
> 
> Cheers,
> Steve
> 
> 
> On 09/05/2013 12:04 PM, David Johnston wrote:
>> It would help to at least know against which version of the database you
>> are
>> trying to connect and also what client (with version) you are using.  Any
>> other connection-related information (password based; ident; etc...)
>> would
>> be helpful as well.
>>
>> David J.
>>
>>

Which client (not just the version):

psql; jdbc; .net; libpq

I am not surprised that an old client talking to a new server would cause a
protocol error to be issued.  

I'm doubtful you will get much help on the mailing lists for something like
this unless you are much more detailed in describing your environment and
usage - at the least I am unable to provide much further help myself. 
On-list trouble shooting is difficult and given the version of the (unnamed)
software involved likely few people will want to try.  

If you cannot upgrade the client driver to match the server you should at
least try and enumerate what kinds of things you have changed and try and
narrow down whether this is a client, server, or machine specific issue.

Searching the source code for where this error is thrown (likely server but
maybe client) would be a good start.  Someone familiar with the codebase may
be able to handle that particular request though I am not currently setup to
do so.

HTH

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/invalid-frontend-message-type-136-tp5769775p5770343.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Need help with Inet type

[Eric Lamer]

Hi,

   I would like to know if there is a way to do what I need with 
Postgress.

   I have a table with logs and a table with a list of subnets.

   Right now I do the following query in my table:

   Select * From logs Where src_ip << '10.0.0.0/24' OR src_ip << 
'10.1.0.0/24' OR src_ip << '172.168.1.0/28';

   I would like to simplify that query and use a table instead as the 
source of the subnet I want to check.

   I have a table that contain a list of subnet and zone name:

   ZONE_a   10.0.0.0/24
   ZONE_a10.1.0.0/24
   ZONE_a172.16.1.0/28
   ZONE_b10.2.0.0/24
   ZONE_b10.3.0.0/24

  I can do something like:

   Select * From logs Where src_ip IN (Select ip from ip_table where zone 
= 'ZONE_a');

   Of course that does not work since it does not work with Inet type and 
I cannot use << because I have more than 1 row return from the second 
select.

   Is there a way to achive that?

Thanks.


___
Eric Lamer
IT Security Specialist
INTACT Financial Corporation
(450) 778-9580 ext. 3744

Re: [GENERAL] Streaming replication slave crash

[Mahlon E. Smith]

On Mon, Sep 09, 2013, Jeff Davis wrote:
> 
> You may have seen only partial information about that bug and the fix.

Yep, I totally glazed over the REINDEX.  Giving it a go -- thank you!

--
Mahlon E. Smith  
http://www.martini.nu/contact.html


pgpIXe1Uec5OO.pgp
Description: PGP signature

Re: [GENERAL] invalid frontend message type 136

[Steve Crawford]

Sorry, I should have included that. The error occurred when an older 
client running 8.3.7 (I know, ferreting and finishing upgrades on 
clients with old libraries is in progress) on CentOS 5.3 (32-bit). Of 
all the machines connecting to the server, this one pretty lightly 
loaded and typically creates a new connection to the server somewhat 
over 10,000 times/week. This is the first instance of the error.


These machines/connections are buried behind firewalls in an automated 
system and this particular connection is password-less.


The server is PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by 
gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit.


Cheers,
Steve


On 09/05/2013 12:04 PM, David Johnston wrote:

It would help to at least know against which version of the database you are
trying to connect and also what client (with version) you are using.  Any
other connection-related information (password based; ident; etc...) would
be helpful as well.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/invalid-frontend-message-type-136-tp5769775p5769778.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.






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

Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Jeff Janes]

On Tue, Sep 10, 2013 at 6:57 AM, Chris Curvey 
wrote:
> From: Marcin Mańk [mailto:marcin.m...@gmail.com]
>
>
> > Is it crashing on a specific database object? pg_restore -v will tell
you
> > how far it went. Then try to restore only that object. Is it perhaps
> > crashing on a specific row?
> >
>
>
>
> Good advice.  I turned on –verbose, and got a ton of output, ending with:
>
>
>
> pg_restore: setting owner and privileges for FK CONSTRAINT
> user_id_refs_id_7ceef80f
>
> pg_restore: setting owner and privileges for FK CONSTRAINT
> user_id_refs_id_dfbab7d
>
> pg_restore: [archiver (db)] could not execute query: no connection to the
> server
>
> Command was: -- Completed on 2013-09-09 11:35:16 EDT
>

What does the server log say?  It should tell you why the server is
restarting.

Cheers,

Jeff

Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Chris Curvey]

From: Jeff Janes [mailto:jeff.ja...@gmail.com]
Sent: Tuesday, September 10, 2013 1:26 PM
To: Chris Curvey
Cc: Marcin Mańk; pgsql-general@postgresql.org
Subject: Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

On Tue, Sep 10, 2013 at 6:57 AM, Chris Curvey 
mailto:ccur...@zuckergoldberg.com>> wrote:
> From: Marcin Mańk [mailto:marcin.m...@gmail.com]
>
>
> > Is it crashing on a specific database object? pg_restore -v will tell you
> > how far it went. Then try to restore only that object. Is it perhaps
> > crashing on a specific row?
> >
>
>
>
> Good advice.  I turned on –verbose, and got a ton of output, ending with:
>
>
>
> pg_restore: setting owner and privileges for FK CONSTRAINT
> user_id_refs_id_7ceef80f
>
> pg_restore: setting owner and privileges for FK CONSTRAINT
> user_id_refs_id_dfbab7d
>
> pg_restore: [archiver (db)] could not execute query: no connection to the
> server
>
> Command was: -- Completed on 2013-09-09 11:35:16 EDT
>
What does the server log say?  It should tell you why the server is restarting.

Cheers,

Jeff

Great thought.  Looking through the logs, it appears that all my failures are 
on a CREATE INDEX.  Usually on my biggest table, but often on another table.

2013-09-10 10:09:46 EDT ERROR:  canceling autovacuum task
2013-09-10 10:09:46 EDT CONTEXT:  automatic analyze of table 
"certified_mail_ccc2.public.cm_status_history"
2013-09-10 10:15:13 EDT LOG:  server process (PID 14386) was terminated by 
signal 11: Segmentation fault
2013-09-10 10:15:13 EDT DETAIL:  Failed process was running: CREATE INDEX 
cm_envelope_tracking_number ON cm_envelope USING btree (tracking_number);



2013-09-10 10:15:13 EDT LOG:  terminating any other active server processes
2013-09-10 10:15:13 EDT WARNING:  terminating connection because of crash of 
another server process
2013-09-10 10:15:13 EDT DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.

I cannot square this with the fact that when I echo the commands, the last 
echoed command is about setting privileges.

Another development (possibly unrelated):  I tried *dumping* with 
–no-privileges –no-tablespace –no-owner, and the restore went fine.


Disclaimer

THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail 
is attorney-client privileged and confidential, intended only for the use of 
the individual or entity named above. If the reader of this message is not the 
intended recipient, or the employee or agent responsible to deliver it to the 
intended recipient, you are here by notified that any dissemination, 
distribution or copying of this communication is strictly prohibited. If you 
have received this communication in error, please notify us immediately by 
reply e-mail or by telephone (908) 233-8500.Thank you..

[GENERAL] PG9.1 Static build over Windows

[Muhammad Bashir Al-Noimi]

Howdy,

I want to link Postgresql client to my Qt project but I noticed that
C:/PostgreSQL/9.1/lib/libpq.lib points to dynamic libaray (libpq.dll) which
is not suiatble for static linking.

How can I link statically my application with Postgresql client?

P.S.
I'm using mingw48_32 not msvs; usually mingw48_32 uses *.a files for static
linking not *.lib
Build log of my project is:

mingw32-make[1]: Leaving directory
'F:/build-TestSSLConnection-Satic_postgresql-Release'
mingw32-make[1]: *** No rule to make target 'C:/PostgreSQL/9.1/lib/pq.lib',
needed by 'release\TestSSLConnection.exe'.  Stop.
makefile:34: recipe for target 'release' failed
mingw32-make: *** [release] Error 2
17:12:37: The process "C:\Qt\Qt5.1.1\Tools\mingw48_32\bin\mingw32-make.exe"
exited with code 2.
Error while building/deploying project TestSSLConnection (kit:
Satic-postgresql)
When executing step 'Make'
17:12:37: Elapsed time: 00:22.

Thanks in advance for help.

-- 
Best Regards
Muhammad Bashir Al-Noimi

Re: [GENERAL] Call for design: PostgreSQL mugs

[Karsten Hilbert]

> ...I wonder how long it will be before we have mugs where you can
> actually tap the logo with a finger and get send to the website!

A QR code is as close as it gets these days.

Karsten Hilbert


-- 
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] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Chris Curvey]

> >
> > Good advice.  I turned on –verbose, and got a ton of output, ending with:
> >
> > pg_restore: setting owner and privileges for FK CONSTRAINT
> > user_id_refs_id_7ceef80f
> >
> > pg_restore: setting owner and privileges for FK CONSTRAINT
> > user_id_refs_id_dfbab7d
> >
> > pg_restore: [archiver (db)] could not execute query: no connection to
> > the server
> >
> >  Command was: -- Completed on 2013-09-09 11:35:16 EDT
> >
> > pg_restore: [archiver (db)] could not execute query: no connection to
> > the server

This was a red herring.  That last constraint that was listed is the last thing 
in the dump/restore (at least according to --list).  Perhaps at the end of 
pg_restore, it's trying to send the comment to the database ("completed on...") 
and that's confusing things?   It does appear that the entire database has been 
restored, but the cluster restart is disconcerting.

>
>
> At this point I would be more worried about the above, 'no connection to
> server'.

Yep, that would be the point where the cluster is restarting.

> --no-owner does not mean that ownership is not set, just that the ownership
> from the source database is not carried over.
>
> http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html
>
> -O
> --no-owner
> Do not output commands to set ownership of objects to match the original
> database. By default, pg_restore issues ALTER OWNER or SET SESSION
> AUTHORIZATION statements to set ownership of created schema elements.
> These statements will fail unless the initial connection to the database is
> made by a superuser (or the same user that owns all of the objects in the
> script). With -O, any user name can be used for the initial connection, and
> this user will own all the created objects.

Does this mean that the --no-owner is a command flag for pg_dump, but is 
ignored by pg_restore?   Let me run a test and see...

Disclaimer

THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail 
is attorney-client privileged and confidential, intended only for the use of 
the individual or entity named above. If the reader of this message is not the 
intended recipient, or the employee or agent responsible to deliver it to the 
intended recipient, you are here by notified that any dissemination, 
distribution or copying of this communication is strictly prohibited. If you 
have received this communication in error, please notify us immediately by 
reply e-mail or by telephone (908) 233-8500.Thank you..

-- 
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] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Adrian Klaver]

On 09/10/2013 06:57 AM, Chris Curvey wrote:

*From:*Marcin Mańk [mailto:marcin.m...@gmail.com]
*Sent:* Monday, September 09, 2013 8:30 PM
*To:* Chris Curvey
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

On Mon, Sep 9, 2013 at 4:00 PM, Chris Curvey mailto:ccur...@zuckergoldberg.com>> wrote:

But I'm having troubles with the 9.2 server crashing when I'm
restoring the dump.  I'm using the 9.2 version of pg_dump.  I've
tried restoring a custom-format dump with pg_restore, and I've tried
restoring  a text-format dump with pqsl, and both of them are
crashing on me.

The data is too sensitive for me to submit a database dump to the
community, but I'd like to submit a stack trace, in the hopes that
someone might be able to figure out what's going on.  But I'm having
some trouble getting this done.

Is it crashing on a specific database object? pg_restore -v will tell
you how far it went. Then try to restore only that object. Is it perhaps
crashing on a specific row?

Try producing a self contained test case (like only the culprit table,
anonymized).

Regards

Marcin Mańk

Good advice.  I turned on –verbose, and got a ton of output, ending with:

pg_restore: setting owner and privileges for FK CONSTRAINT
user_id_refs_id_7ceef80f

pg_restore: setting owner and privileges for FK CONSTRAINT
user_id_refs_id_dfbab7d

pg_restore: [archiver (db)] could not execute query: no connection to
the server

 Command was: -- Completed on 2013-09-09 11:35:16 EDT

pg_restore: [archiver (db)] could not execute query: no connection to
the server



At this point I would be more worried about the above, 'no connection to 
server'.




 Command was: --

-- PostgreSQL database dump complete

–

Which I find really odd, because I specified –no-owner –no-privileges
–no-tablespace


--no-owner does not mean that ownership is not set, just that the 
ownership from the source database is not carried over.


http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html

-O
--no-owner
Do not output commands to set ownership of objects to match the original 
database. By default, pg_restore issues ALTER OWNER or SET SESSION 
AUTHORIZATION statements to set ownership of created schema elements. 
These statements will fail unless the initial connection to the database 
is made by a superuser (or the same user that owns all of the objects in 
the script). With -O, any user name can be used for the initial 
connection, and this user will own all the created objects.




chris@mu:/sdb$ pg_restore --dbname=certified_mail_ccc2 --format=c
--verbose --clean --no-owner --no-privileges --no-tablespaces -h mu -p
5434 cm_Mon.backup

So now I’m up to three questions.  (Why the crash?  How to get
backtrace?  Why are we applying permissions when I said not to?)  I
guess that’s the nature of the universe. Let me see if I can figure out
which table that is and try to create a test case.





--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] FW: Single Line Query Logging

[Giuseppe Broccolo]

From: emreozt...@outlook.com
To: /pgsql-general@postgresql.org/
Subject: Single Line Query Logging
Date: Wed, 10 Jul 2013 13:16:13 +0300

Hello all,

Is there a parameter to log any SQL query as a single line in audit 
logs? I have some problems in my SIEM application. If a DBA sends the 
query as a single line I can gather the whole query, but if he enters like


UPDATE x  ...
  y=Z ..
  where ..

I only get the line starts with UPDATE then I can not see what is 
really changed in my SIEM logs. I have heard that there is a parameter 
do what I mean. Do you agree?


No. There's no parameter to be set for log parsing in a simple way, 
specially for multi-raws query. A possible solution is to use syslog 
instead of stderr: syslog stores each log element as an independent item.

You can try setting

log_destination = 'syslog'
redirect_stderr = off

Then you have to setup syslog by editing your syslog conf (On Debian: 
/etc/rsyslog.d/50-default.conf): add this new line (supposing your log 
directory is /var/log/pgsql/, and 'local0' is set in "syslog_facility" 
parameter in your postgres.conf - do a check)


LOCAL0.*-/var/log/pgsql

and in the "catch all log files" area add

LOCAL0.none

then restart syslog (sudo /etc/init.d/rsyslog restart). I've tried it, 
and it works!


Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it

Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

[Chris Curvey]

From: Marcin Mańk [mailto:marcin.m...@gmail.com]
Sent: Monday, September 09, 2013 8:30 PM
To: Chris Curvey
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] help getting a backtrace from 9.2 on Ubuntu 13.04?

On Mon, Sep 9, 2013 at 4:00 PM, Chris Curvey 
mailto:ccur...@zuckergoldberg.com>> wrote:
But I'm having troubles with the 9.2 server crashing when I'm restoring the 
dump.  I'm using the 9.2 version of pg_dump.  I've tried restoring a 
custom-format dump with pg_restore, and I've tried restoring  a text-format 
dump with pqsl, and both of them are crashing on me.

The data is too sensitive for me to submit a database dump to the community, 
but I'd like to submit a stack trace, in the hopes that someone might be able 
to figure out what's going on.  But I'm having some trouble getting this done.

Is it crashing on a specific database object? pg_restore -v will tell you how 
far it went. Then try to restore only that object. Is it perhaps crashing on a 
specific row?

Try producing a self contained test case (like only the culprit table, 
anonymized).

Regards
Marcin Mańk

Good advice.  I turned on –verbose, and got a ton of output, ending with:

pg_restore: setting owner and privileges for FK CONSTRAINT 
user_id_refs_id_7ceef80f
pg_restore: setting owner and privileges for FK CONSTRAINT 
user_id_refs_id_dfbab7d
pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: -- Completed on 2013-09-09 11:35:16 EDT


pg_restore: [archiver (db)] could not execute query: no connection to the server
Command was: --
-- PostgreSQL database dump complete
–

Which I find really odd, because I specified –no-owner –no-privileges 
–no-tablespace
chris@mu:/sdb$ pg_restore --dbname=certified_mail_ccc2 --format=c --verbose 
--clean --no-owner --no-privileges --no-tablespaces -h mu -p 5434 cm_Mon.backup

So now I’m up to three questions.  (Why the crash?  How to get backtrace?  Why 
are we applying permissions when I said not to?)  I guess that’s the nature of 
the universe. Let me see if I can figure out which table that is and try to 
create a test case.

Disclaimer

THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail 
is attorney-client privileged and confidential, intended only for the use of 
the individual or entity named above. If the reader of this message is not the 
intended recipient, or the employee or agent responsible to deliver it to the 
intended recipient, you are here by notified that any dissemination, 
distribution or copying of this communication is strictly prohibited. If you 
have received this communication in error, please notify us immediately by 
reply e-mail or by telephone (908) 233-8500.Thank you..

Re: [GENERAL] Call for design: PostgreSQL mugs

[Alban Hertroys]

On 10 September 2013 15:09, Vincent Veyron  wrote:

> Le lundi 09 septembre 2013 à 14:17 -0700, John R Pierce a écrit :
> > On 9/9/2013 2:07 PM, Basil Bourque wrote:
>
> > >
> > > --> Lift the rounded-corner blue bar off the top of the
> > > PostgreSQL.org web site:
> > > http://www.postgresql.org/
> > > and wrap around a white mug.
> > >
> > >
> > > So, the left-handed see the elephant head and "PostgreSQL".
> > > The right-handed see "The world's most advanced open source
> > > database.".
> >
> >
> > I like that.  could even add a grey bar under it like the menu bar on
> > the website, but with buzzwords associated with postgresql, like
> > 'Transactional   Data Integrity Extensible"   or whatever.
>
> Nice for professionals, but the simplicity of the OP's proposition makes
> for a nice brand recognition, I find?
>
> I would suggest to write 'postgresl.org' for the message.
>

I agree that the website where the logo comes from obviously doesn't need a
written URL on the page, but it's a good addition to a mug.

Instead. I'd suggest to add that URL to the postgres website lower on the
same side (at the bottom, for example). I'd use the same font as in the
logo using a dark-gray colour.

...I wonder how long it will be before we have mugs where you can actually
tap the logo with a finger and get send to the website!

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

Re: [GENERAL] Call for design: PostgreSQL mugs

[Vincent Veyron]

Le lundi 09 septembre 2013 à 14:17 -0700, John R Pierce a écrit :
> On 9/9/2013 2:07 PM, Basil Bourque wrote:

> > 
> > --> Lift the rounded-corner blue bar off the top of the
> > PostgreSQL.org web site:
> > http://www.postgresql.org/
> > and wrap around a white mug.
> > 
> > 
> > So, the left-handed see the elephant head and "PostgreSQL".
> > The right-handed see "The world's most advanced open source
> > database.".
> 
> 
> I like that.  could even add a grey bar under it like the menu bar on
> the website, but with buzzwords associated with postgresql, like
> 'Transactional   Data Integrity Extensible"   or whatever.

Nice for professionals, but the simplicity of the OP's proposition makes
for a nice brand recognition, I find?

I would suggest to write 'postgresl.org' for the message.

-- 
Salutations, Vincent Veyron 
http://marica.fr/ 
Gestion des contrats, des contentieux juridiques et des sinistres
d'assurance



-- 
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] Making substrings uppercase

[Merlin Moncure]

On Tue, Sep 10, 2013 at 5:51 AM, Oliver Kohll - Mailing Lists
 wrote:
> On 9 Sep 2013, at 21:03, Alvaro Herrera  wrote:
>
> select string_agg(case when words like '*%*' then upper(btrim(words, '*'))
> else words end, ' ')
> from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy*
> dog', ' ') as words;
>
>  string_agg
> --
> The QUICK BROWN fox jumped over the LAZY dog
>
>
> That's quite elegant. In the end I exported and used PERL, as some of my
> 'words' had spaces (they were ingredients like monosodium glutamate), but
> you could probably do a more complex regex in regexp_split_to_table to cope
> with that, or use pl/perl as previously suggested.

IMO, pl/perl is the way to go.  Being able to use postgres functions
to transform matched regex expressions would be just wonderful
although I wonder how fast it would be or if it's even possible.

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] Making substrings uppercase

[Oliver Kohll - Mailing Lists]

On 9 Sep 2013, at 21:03, Alvaro Herrera  wrote:

> select string_agg(case when words like '*%*' then upper(btrim(words, '*')) 
> else words end, ' ')
> from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy* 
> dog', ' ') as words;
> 
>  string_agg  
> --
> The QUICK BROWN fox jumped over the LAZY dog

That's quite elegant. In the end I exported and used PERL, as some of my 
'words' had spaces (they were ingredients like monosodium glutamate), but you 
could probably do a more complex regex in regexp_split_to_table to cope with 
that, or use pl/perl as previously suggested.

Thanks
Oliver
www.agilebase.co.uk

Re: [GENERAL] plpgsql code doen't work

[Giuseppe Broccolo]

Il 10/09/2013 10:46, Beena Emerson ha scritto:

Hello,

Try changing the variable left to something other like left_val. It 
will work.

Maybe the problem is because LEFT is a keyword.
Yes, left() is a function returning a 'text'. There's a conflict when 
you define it as an 'integer'...


Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it



--
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] FW: Single Line Query Logging

[Albe Laurenz]

Emre ÖZTÜRK wrote:

> I have sent below question months ago but I could not get any reply from you 
> experts. I will very
> appreciated if you can help.
> 
> PS: I have tried debug_pretty_print = off but I did not work.

> Is there a parameter to log any SQL query as a single line in audit logs? I 
> have some problems in my
> SIEM application. If a DBA sends the query as a single line I can gather the 
> whole query, but if he
> enters like
> 
> UPDATE x  ...
>   y=Z ..
>   where ..
> 
> I only get the line starts with UPDATE then I can not see what is really 
> changed in my SIEM logs. I
> have heard that there is a parameter do what I mean. Do you agree?

There is no way to modify queries like that in the PostgreSQL log.

You can set log_destination = 'csvlog' to get a PostgreSQL log in CSV
format that can be parsed more easily.

I don't know what the "SIEM logs" are that you talk about.
If they log only part of the query, I'd say that they are broken
and should be fixed.

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] invalid resource manager ID in primary checkpoint record

[ascot.m...@gmail.com]

Hi, any idea? can you  please advise?

On 10 Sep 2013, at 3:22 AM, ascot.m...@gmail.com wrote:

> Hi,
> 
> For special testing reason, I am trying to restore PG from a backup that the 
> basebase is from Standby and WAL files are from Master.  During recovery 
> phase, for every WAL file process, it returned 'invalid resource manager ID 
> in primary checkpoint record' and paused, I had to manually run 
> "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set PG to 
> ignore this kind of errors.
> 
> LOG:  database system was shut down at 2013-09-10 03:06:29 :  starting 
> archive recovery
> LOG:  restored log file "000100B900CA" from archive
> LOG:  invalid resource manager ID in primary checkpoint record
> LOG:  invalid secondary checkpoint link in control file
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 17969) was terminated by signal 6: Aborted
> LOG:  aborting startup due to startup process failure
> .
> LOG:  restored log file "000100B900CB" from archive
> LOG:  invalid resource manager ID in primary checkpoint record
> LOG:  invalid secondary checkpoint link in control file
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 17981) was terminated by signal 6: Aborted
> LOG:  aborting startup due to startup process failure
> .
> LOG:  restored log file "000100B900CC" from archive
> .
> LOG:  restored log file "000100B900CD" from archive
> 
> 
> Please advise.
> regards
> 
> 
> 
> 



-- 
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] plpgsql code doen't work

[Beena Emerson]

Hello,

Try changing the variable left to something other like left_val. It will
work.
Maybe the problem is because LEFT is a keyword.


Beena Emerson

[GENERAL] FW: Single Line Query Logging

[Emre ÖZTÜRK]

Hello all,

I have sent below question months ago but I could not get any reply from you 
experts. I will very appreciated if you can help.

PS: I have tried debug_pretty_print = off but I did not work.





Emre ÖZTÜRK


From: emreozt...@outlook.com
To: pgsql-general@postgresql.org
Subject: Single Line Query Logging
Date: Wed, 10 Jul 2013 13:16:13 +0300




Hello all,

Is there a parameter to log any SQL query as a single 
line in audit logs? I have some problems in my SIEM application. If a 
DBA sends the query as a single line I can gather the whole query, but 
if he enters like




UPDATE x  ...
  y=Z ..
  where ..

I
 only get the line starts with UPDATE then I can not see what is really 
changed in my SIEM logs. I have heard that there is a parameter do what I
 mean. Do you agree?

Regards, 






Emre ÖZTÜRK

  
  

[GENERAL] plpgsql code doen't work

[janek12]

Hi, 
 
I found following code:

create or replace function plpgsql_edit_distance(stra text, strb text)
returns integer as $$
declare
rows integer;
cols integer;
begin
rows := length(stra);
cols := length(strb);

IF rows = 0 THEN
return cols;
END IF;
IF cols = 0 THEN
return rows;
END IF;

declare
row_u integer[];
row_l integer[];
diagonal integer;
upper integer;
left integer;
begin
FOR i in 0..cols LOOP
row_u[i] := i;
END LOOP;

FOR i IN 1..rows LOOP
row_l[0] := i;
FOR j IN 1..cols LOOP
IF substring (stra, i, 1) = substring (strb, j, 1) THEN
diagonal := row_u[j-1];
else
diagonal := row_u[j-1] + 1;
END IF;
upper := row_u[j] + 1;
left := row_l[j-1] + 1;
row_l[j] := int4smaller(int4smaller(diagonal, upper), left);
END LOOP;
row_u := row_l;
END LOOP;
return row_l[cols];
end;
end
$$ language 'plpgsql' immutable strict;

Does anyone know why the colums :"row_l[j] := int4smaller(int4smaller(diagonal, 
upper), left);" doesn't work.
 
Janek Sendrowski


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

Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs

[roppert]

On Mon, Sep 9, 2013 at 10:22 PM, patrick keshishian  wrote:
>
> If printing inside the mug is too difficult, cost prohibitive, then stick
> with the smiling elephant at the outside bottom of the mug.
>
> This type of a design would be a good ice-breaker/conversation
> starter in office meetings, etc.

This is a very nice idea. +1

Regards,
roppert

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


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