[GENERAL] Rule Question

[Andrew Bartley]

Hi all,

Hope this question is not too stupid but..

I am trying to do something like this

create table cats (a text,b text);

create rule cats_test as on update to cats do set a = new.b;

Can i manipulate column "a" sort of like this...  or is  there a better way.



I would like to do this as the construction of the new.b value is complex
and time consuming, so I just want to do it once.

update cats
set b = something_complex_and_time_consuming(b);


Thanks

Andrew

Re: [GENERAL] Rule Question

[Andrew Bartley]

Thanks All,

And thanks Tom, I did not realise a rule worked in that manner.  Will now
take that into account in the future.

Thanks

Andrew


On 26 July 2013 02:02, Giuseppe Broccolo
wrote:

>
>  Unrelated to the OP's question, the suggestion above could be more simply
>> rewritten as
>>
>> TG_OP = 'UPDATE'
>> AND NEW.b IS DISTINCT FROM OLD.b
>>
> You're right! :)
>
>
> Giuseppe.
>
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.broccolo@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
>

[GENERAL] Pass where clause to a function

[Andrew Bartley]

Hi all,

Is it possible to create a view or foreign table that will do something
like this

select * from table_x
where x_id = 10;

passing the where "x_id = 10" to a function

sorta like this

select * from api_function('x = 10')

or

 select * from api_function(10)

passing the result set back to the original select

Thanks

Re: [GENERAL] Pass where clause to a function

[Andrew Bartley]

Hi John,

Thanks for the response

I need this because it is a customer requirement.  The underlying tables
the api will query are dynamically created, they are period/content
partitioned and distributed across multiple servers and database types...
not just postgres.  The api/function will determine what
servers/databases/tables (some of them are foreign tables) to query based
on the parameters passed to the api.  The customer wants to use their
current reporting tool that does not support function selects.

I am open to suggestions here.

I understand  this type of method will restrict the type predicates that
can be written at the top level of the query...  But that is ok, we can
work with the restrictions.

I also understand I may have overs simplified the question. So I hope I did
not waste your time

Thanks again

Andrew


On 14 August 2014 15:20, John R Pierce  wrote:

> On 8/13/2014 10:08 PM, Andrew Bartley wrote:
>
>>
>> Is it possible to create a view or foreign table that will do something
>> like this
>>
>> select * from table_x
>> where x_id = 10;
>>
>> passing the where "x_id = 10" to a function
>>
>> sorta like this
>>
>> select * from api_function('x = 10')
>>
>> or
>>
>>  select * from api_function(10)
>>
>> passing the result set back to the original select
>>
>>
> your first version of api_function would have to use that x=10 to
> construct the query and EXECUTE it, then return the recordset,
>
> your second version of the view would just pass 10 in as an argument,
> which could be used for the query select * from table_x where x_id=$1
>
> either way, your view would be select * from api_function(whichever).
>
> but I think you're rather confused here, as I don't see much utility in
> either of these constructs in the form you specified.
>
>
>
> --
> 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
>

[GENERAL] intagg

[Andrew Bartley]

Hi All,

I am trying to use the intagg extension. in 9.1.9

I have created the extension as such "CREATE EXTENSION intagg"

Then tried to use the function int_array_aggregate.

Returns this message

function int_array_aggregate(integer[]) does not exist

select int_array_aggregate(transactions) from x

x being

create table x (transactions int4[]);

Can anyone please advise..

Thanks

Andrew Bartley

Re: [GENERAL] intagg

[Andrew Bartley]

Sorry that should be aggregate int_array_aggregate not function


On 20 June 2013 08:16, Andrew Bartley  wrote:

> Hi All,
>
> I am trying to use the intagg extension. in 9.1.9
>
> I have created the extension as such "CREATE EXTENSION intagg"
>
> Then tried to use the function int_array_aggregate.
>
> Returns this message
>
> function int_array_aggregate(integer[]) does not exist
>
> select int_array_aggregate(transactions) from x
>
> x being
>
> create table x (transactions int4[]);
>
> Can anyone please advise..
>
> Thanks
>
> Andrew Bartley
>

Re: [GENERAL] Deleting all but one row of a list of non-uniques

[Andrew Bartley]

Try this.

Create a temp table with a list of the duplicate unid's

eg

create temp table duplicates
as
select min(oid) as oid_val, unid from 
group by unid having count(*) > 1;

Then isolate the unwanted rows

update 
set unid  = null  
from duplicates
where .unid = duplicates.unid
and oid_val <> .oid

Then delete them

delete from  where unid is null

Thanks

Andrew

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Zak McGregor
Sent: Wednesday, 23 June 2004 10:18 AM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Deleting all but one row of a list of non-uniques


Hi all


I have a table, for simplicity's sake containing one field, called unid.


for example, select unid, oid from table gives me something like this:

   unid   |   oid
 -+-
 XNO24ORK | 40276607
 XNPGJDPU | 40276673 *
 XNPGJDPU | 40276674 *
 XNXAAVQ2 | 40277583
 ZAZAFAA4 | 40276600
 ZAZV5UG4 | 40276446
 ZD66A1LL | 40277162
 ZDXZ27RS | 40277454
 ZEKRT3GM | 40277739 *
 ZEKRT3GM | 40277740 *
 ZEKRT3GM | 40277741 *

(I've marked the rows with duplicated unids with the * to the right)

I'd like to delete rows in such a way that one (and any one) row for each
unid
remains, and all other duplicates are removed. Does anyone have any ideas
that
may help me here please?

slecting distinct is not helpful as in reality there are other fields which
contain data like timestamps that will differ but I don't mind which one
remains, but I'd need to work with them on selects to the table afterwards.

 I've tried:

 delete from table where oid in (select p1.oid from table p1, table p2 where
p1.oid != p2.oid and p1.unid=p2.unid);

which only works in a few cases - I suspect where there are only two rows
with
the same unid. Is it even possible?

Alternatively, can I get the \copy command to ignore attempts to insert
duplicated rows into a UNIQUE column instead of aborting the copy? Not sure
if
any of the options that can be supplied to the table at creation time for
unique
will help here.

Thanks.

Ciao

Zak

--

http://www.carfolio.com/Searchable database of 10 000+ car specs


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




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

[GENERAL] Function Logging

[Andrew Bartley]

Hi All,



We have recently upgraded postgres from 7.2 to 8.3.



I am struggling with the logging options when it comes to functions.



How do log statements from within a Function?



Thanks



Andrew Bartley

[GENERAL] Function Logging

[Andrew Bartley]

Hi All,
 
We have recently upgraded postgres from 7.2 to 8.3.
 
I am struggling with the logging options when it comes to functions.
 
How do log statements from within a Function?
 
Thanks
 
Andrew Bartley

Re: [GENERAL] Function Logging

[Andrew Bartley]

Thanks Craig fro you reply,


7.2 does have statement logging from within functions.

How? these are the settings we use in our production environments. (7.2)

debug_level = 2
log_connections = off
log_pid = on
log_timestamp = on
syslog = 0

Why, we use logging fro production problem resolution.

We have been using Postgres for 8 years, hundreds of functions have been
written, we have become reliant on logging for production support.

Thanks

Andrew Bartley

PS on this note, how do we log PIDS?  This is another "need to have" for our
production systems.  We log user activity and trace DB activity through the
PIDS.





2009/8/17 Craig Ringer 

> On 17/08/2009 10:32 AM, Andrew Bartley wrote:
>
>> Hi All,
>>
>> We have recently upgraded postgres from 7.2 to 8.3.
>>
>> I am struggling with the logging options when it comes to functions.
>>
>> How do log statements from within a Function?
>>
>
> A PL/PgSQL function?
>
> You don't, generally.
>
> Perhaps we should back up a step or two. Did you have statement logging
> within PL/PgSQL functions in 7.2? If so, how? Why do you need statement
> logging within functions? What are you trying to achieve with it?
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Function Logging

[Andrew Bartley]

Thanks Alvaro,

I still need to be able to log statements from within functions...  Can you
help with that?

2009/8/18 Alvaro Herrera 

> Andrew Bartley escribió:
>
> > PS on this note, how do we log PIDS?  This is another "need to have" for
> our
> > production systems.  We log user activity and trace DB activity through
> the
> > PIDS.
>
> See log_line_prefix.  You can use something like '%p ' (note the space
> at the end), or stuff like the session identifier as described in the
> docs.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

Re: [GENERAL] Function Logging

[Andrew Bartley]

So the information i have thus far is that, I am not easily able to log the
statements from a function.

Does anyone know why it was removed... that is it 7.2 logged this
information.

Thanks Again

Andrew Bartley

2009/8/18 Andrew Bartley 

> Thanks Alvaro,
>
> I still need to be able to log statements from within functions...  Can you
> help with that?
>
> 2009/8/18 Alvaro Herrera 
>
> Andrew Bartley escribió:
>>
>> > PS on this note, how do we log PIDS?  This is another "need to have" for
>> our
>> > production systems.  We log user activity and trace DB activity through
>> the
>> > PIDS.
>>
>> See log_line_prefix.  You can use something like '%p ' (note the space
>> at the end), or stuff like the session identifier as described in the
>> docs.
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/
>> The PostgreSQL Company - Command Prompt, Inc.
>>
>
>

[GENERAL] Fatal error on start

[Andrew Bartley]

does anyone know what this is?

Trying to start postgres version:
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2

2009-10-06 15:17:21 EST 3757  FATAL:  XX000: could not open directory
"pg_tblspc": Unknown error 530
2009-10-06 15:17:21 EST 3757  LOCATION:  ReadDir, fd.c:1460

Re: [GENERAL] Fatal error on start

[Andrew Bartley]

Thanks,

I found it, a permissions issue using sudo to start postgres.

Thanks anyway.

2009/10/6 纪晓曦 

> Did you delete some log or data in your PGDATA directory?
>
> 2009/10/6 Andrew Bartley 
>
> does anyone know what this is?
>>
>> Trying to start postgres version:
>> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
>> (Debian 4.3.2-1.1) 4.3.2
>>
>> 2009-10-06 15:17:21 EST 3757  FATAL:  XX000: could not open directory
>> "pg_tblspc": Unknown error 530
>> 2009-10-06 15:17:21 EST 3757  LOCATION:  ReadDir, fd.c:1460
>>
>>
>

[GENERAL] Redundant database objects.

[Andrew Bartley]

Hi all,

Our project has been running for 10 years now.

We have a large number of orphaned or redundant tables, views, and
functions, due to many years of inadequate source management.

We are running " PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC
gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 "

Is there an effective way to identify these objects using the stats tables?
 Something like a last accessed/used or some such column?

Any suggestions welcomed.

Thanks

Andrew Bartley
Aimstats Pty Ltd

Re: [GENERAL] Redundant database objects.

[Andrew Bartley]

Thanks Joe,

Unfortunately these views only give me what appears to be a certain time
frame.  This does not help all that much.  It will give a list of tables,
indexes and sequences that have been used in the time frame, so that is at
least a start.

It would be good if there was a timestamp (last accessed) that would give me
a clearer indication.

Thanks

Andrew

On 13 July 2010 08:46, Joe Conway  wrote:

> On 07/12/2010 02:40 PM, Andrew Bartley wrote:
> > We have a large number of orphaned or redundant tables, views, and
> > functions, due to many years of inadequate source management.
> >
> > We are running " PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC
> > gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 "
> >
> > Is there an effective way to identify these objects using the stats
> > tables?  Something like a last accessed/used or some such column?
>
> Maybe pg_statio* views?
>  http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html
>
> Joe
>
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source
> Training, Service, Consulting, & Support
>
>

Re: [GENERAL] Redundant database objects.

[Andrew Bartley]

Thanks Greg,

It seems that the underlying stats tables are reset on a periodic basis, can
i stop this process? Is it a .conf setting?  I have had a good look around,
nothing sticks out. If I can stop it, then i could use pg_stat_reset() then
monitor the stat views over an extended period without them being reset by
some periodic job.  That, at the moment, is my main concern.

Thanks again.

Also i need to find similar information regarding functions and views
 Any suggestions?

Thanks

Andrew Bartley


On 13 July 2010 09:45, Greg Smith  wrote:

> Andrew Bartley wrote:
>
>> Unfortunately these views only give me what appears to be a certain time
>> frame.  This does not help all that much.  It will give a list of tables,
>> indexes and sequences that have been used in the time frame, so that is at
>> least a start.
>>
>
> You can use pg_stat_reset() to set those back to 0 again and then see what
> actually gets used moving forward from the point you do that.  That's a
> reasonable idea to do anyway to make all those statistics better reflect
> recent activity rather than historical.  Just be warned that it will screw
> up many monitoring systems if you have them pointed toward those statistics
> tables and grabbing snapshots, some will view the reset as the values going
> negative which doesn't make any real-world sense.
>
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us
>
>

Re: [GENERAL] Redundant database objects.

[Andrew Bartley]

Thanks Alexander,

Wish i had thought of that.

I still need some way of finding redundant functions

Thanks again

Andrew


On 13 July 2010 15:38, Alexander Pyhalov  wrote:

> Hello.
> When we moved old projects from postgresql 7.x to 8.4, I just looked at
> modification time for files in base// . So, I could determine,
> that some databases were inactive (precisely,not updated) for about a year
> and move them to archive...
>
> Andrew Bartley wrote:
>
>  Is there an effective way to identify these objects using the stats
>> tables?  Something like a last accessed/used or some such column?
>> Any suggestions welcomed.
>> Thanks
>>
>> Andrew Bartley
>> Aimstats Pty Ltd
>>
>>
>
> --
> С уважением,
> Александр Пыхалов,
> системный администратор ЮГИНФО ЮФУ.
>
>

Re: [GENERAL] Redundant database objects.

[Andrew Bartley]

Thanks to all that replied,

I used Joe Conway's suggestion, using grep and an extracted list of tables,
functions and views form the DB.  It worked very well.

I will attach the code I used to this thread once complete.

Again Thanks

Andrew Bartley

On 14 July 2010 00:43, Greg Smith  wrote:

> Andrew Bartley wrote:
>
>> It seems that the underlying stats tables are reset on a periodic basis,
>> can i stop this process? Is it a .conf setting?
>>
>
> Up until PostgreSQL 8.2 there's a setting named stats_reset_on_server_start
> that clears everything when the server stops:
> http://www.postgresql.org/docs/8.2/static/runtime-config-statistics.html
>
> If you're on that version or earlier and it's turned on, there's your
> problem.  This went away in 8.3.
>
>
>  Also i need to find similar information regarding functions and views
>>  Any suggestions?
>>
>
> Some suggestions already popped up here for functions.  Views are tougher
> because they essentially work like a macro substitution:  the content of the
> view gets substituted into the query where it appears, and off the query
> planner goes.  That's why there's no statistics about them, they don't
> actually exist as objects that things are executed against.  I don't know of
> any way to track their use other than to log all your queries and look for
> them popping up.  A grep against the application source code for them can be
> useful too.
>
> The flip side to that is that eliminating views doesn't really improve
> performance, so it's rarely a top priority to get rid of them--unlike unused
> indexes for example.
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us
>
>