[GENERAL] Determine unused / not referenced large Objects

[Tobias Meyer]

Hello list,

we have a database with several tables containing blobs (as oid), that 
reference large objects in pg_largeobject.
It is my understanding, that the large objects will not be deleted if a row 
containing the oid is deleted - you have to do that yourself in application 
logic. (please correct me if wrong)
I think we might not have done that consistently in the past.

Is it safe to assume that pg_largeobject only contains large objects added by 
application code, or might there be any postgresql-internal objects?

Providing that all BLOB colums are of type oid, I was thinking of getting the 
fields with
select table_name,column_name from information_schema.columns where data_type = 
'oid' and table_name not like 'pg_%' and table_name not like '_pg%';

to create a
select comum1 from table1 UNION select column2 from table2...

and comparing the output with
select distinct loid from pg_largeobject

and subsequently deleting the abandoned ones.

Though we do have backups of course I'm afraid I might be missing something...

regards
Tobias

Re: [GENERAL] Determine unused / not referenced large Objects

[Thomas Kellerer]

Tobias Meyer, 28.11.2012 10:02:

Hello list,

we have a database with several tables containing blobs (as oid),
that reference large objects in pg_largeobject.

It is my understanding, that the large objects will not be deleted if
a row containing the oid is deleted – you have to do that yourself in
application logic. (please correct me if wrong)

I think we might not have done that consistently in the past.

Is it safe to assume that pg_largeobject only contains large objects
added by application code, or might there be any postgresql-internal
objects?



Isn't vacuumlo supposed to do this kind of cleanup?

http://www.postgresql.org/docs/current/static/vacuumlo.html

Regards
Thomas




--
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] Determine unused / not referenced large Objects

[Tobias Meyer]

>
>Isn't vacuumlo supposed to do this kind of cleanup?
>
>http://www.postgresql.org/docs/current/static/vacuumlo.html
>
>Regards
>Thomas

Thank you - I wasn't aware of that utility program - just what I was looking 
for.

Tobias

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

[GENERAL] Database level Replication

[Ranjeet Dhumal]

Hi ,

Am running near about 10 databases on one cluster , out of this i want to
replicate one database to another server where allready some databases are
running , is  any tool is der in postgres which can replicate only one
 database at a time.

-- 
--Regards
  Ranjeet  R. Dhumal

Re: [GENERAL] Database level Replication

[Bèrto ëd Sèra]

Hi,

Slony will do that. Have a look here, any way:

http://www.postgresql.org/docs/9.2/static/different-replication-solutions.html

Cheers
Bèrto

On 28 November 2012 14:52, Ranjeet Dhumal  wrote:
> Hi ,
>
> Am running near about 10 databases on one cluster , out of this i want to
> replicate one database to another server where allready some databases are
> running , is  any tool is der in postgres which can replicate only one
> database at a time.
>
> --
> --Regards
>   Ranjeet  R. Dhumal
>
>



-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.


-- 
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_listening_channels()

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Igor Neyman asked:

[Postgres 9.2]
> How (if it's even possible) can I get listening channels 
> for all sessions/ connections in the database?

No, this is not possible. You can only see items 
from your own session.

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

iEYEAREDAAYFAlC2LRoACgkQvJuQZxSWSsi71ACeOdmbxth+tuF45N2DwoxftnWm
EX8An3ZjjKloJo8M1PdyCuoSLFiHV/Kd
=noR6
-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] GRANT .. VIEW .. documentation

[Wim Bertels]

Hallo,

is there a reason why
the grant syntax for views isn't included in the documentation?
(eg REFERENCES doesnt make sense for a view)

cf
http://www.postgresql.org/docs/9.2/static/sql-grant.html
or
\h grant in psql

GRANT INSERT ON view_x TO user_y;
works this for scripting reasons i suppose
(views are readonly at this point, rules..)


mvg,
Wim


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

[GENERAL] Getting show results into a table

[Little, Douglas]

Is there a way in sql to get the results of the show all command into a table?

I'm expecting  something like
Insert into Config_history as select * from (show all);



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CDCD65.492B5520]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] Getting show results into a table

[Kevin Grittner]

Little, Douglas wrote:

> Is there a way in sql to get the results of the show all command into a table?
> 
> I'm expecting something like
> Insert into Config_history as select * from (show all);

insert into Config_history
  select name, setting, short_desc from pg_settings;

or maybe:

create table config_history as
  select name, setting, short_desc as description from pg_settings;

-Kevin


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

[GENERAL] 'alternatives'

[Christophe Pettus]

In a query plan, I noticed the following:

 Join Filter: (((all_permissions.role_recursive AND (alternatives: 
SubPlan 5 or hashed SubPlan 6)) OR (permitted_e.id = deployed_e.id)) AND (NOT 
(SubPlan 13)))

What's the 'alternatives' line?  Brand new to me!

--
-- Christophe Pettus
   x...@thebuild.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] Getting show results into a table

[Guillaume Lelarge]

On Wed, 2012-11-28 at 12:38 -0600, Little, Douglas wrote:
> Is there a way in sql to get the results of the show all command into a table?
> 

SELECT name, setting, short_desc FROM pg_settings

> I'm expecting  something like
> Insert into Config_history as select * from (show all);
> 

INSERT INTO config_history
  SELECT name, setting, short_desc FROM pg_settings;

That should work.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] Getting show results into a table

[Little, Douglas]

Thanks everybody.Always wondered where the command was sourcing the data.

Thanks

-Original Message-
From: Guillaume Lelarge [mailto:guilla...@lelarge.info] 
Sent: Wednesday, November 28, 2012 3:24 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] Getting show results into a table

On Wed, 2012-11-28 at 12:38 -0600, Little, Douglas wrote:
> Is there a way in sql to get the results of the show all command into a table?
> 

SELECT name, setting, short_desc FROM pg_settings

> I'm expecting  something like
> Insert into Config_history as select * from (show all);
> 

INSERT INTO config_history
  SELECT name, setting, short_desc FROM pg_settings;

That should work.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] 'alternatives'

[Andres Freund]

Hi Christophe,

On 2012-11-28 13:07:12 -0800, Christophe Pettus wrote:
> In a query plan, I noticed the following:
> 
>  Join Filter: (((all_permissions.role_recursive AND
>  (alternatives: SubPlan 5 or hashed SubPlan 6)) OR
>  (permitted_e.id = deployed_e.id)) AND (NOT (SubPlan 13)))

Check the first item of
http://www.postgresql.org/docs/current/interactive/release-9-2.html#AEN110503

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] 'alternatives'

[Christophe Pettus]

Hi, Andres,

Thanks!

On Nov 28, 2012, at 1:58 PM, Andres Freund wrote:

> http://www.postgresql.org/docs/current/interactive/release-9-2.html#AEN110503

Does that apply to views as well?  (This particular plan was not from a 
prepared or PL/pgSQL statement, but did include views.)

--
-- Christophe Pettus
   x...@thebuild.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] pgagent linux install issues

[hartrc]

Hi,
I'm attempting to install pgagent on SLES 11. So far I have downloaded the
pgagent (3.3.0) to the server. Extracted using gunzip and untarred.
I had the sysadmin install cmake 2.6 & wxGTK & wxGTK-devel using yast. When
i run wx-config it shows version 2.8.10

When i attempt to install pgagent I execute

cmake /path/to/source

I get error:

CMake Error at cmake/FindWX.cmake:271 (MESSAGE):
  The selected wxWidgets configuration (version: 2.8, debug: yes, static:
no,
  unicode: yes, modules: base) is not available.
Call Stack (most recent call first):
  CMakeLists.txt:95 (FIND_PACKAGE)


-- Configuring incomplete, errors occurred!

I've also ran ccmake /path/to/source 
with following options:
CMAKE_BUILD_TYPE
 CMAKE_INSTALL_PREFIX /usr/local
 PG_CONFIG_PATH   /postgresql/v91/bin/pg_config
 STATIC_BUILD OFF
 WX_CONFIG_PATH   /usr/bin/wx-config
 WX_DEBUG


Please can someone help as to what I am missing, the error certainly
suggests it is an issue with WxWidgets dependency but I'm not sure what that
issue is?

Thanks
Rob



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pgagent-linux-install-issues-tp5733982.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] pg_listening_channels()

[Igor Neyman]

From: Greg Sabino Mullane [g...@turnstep.com]
Sent: Wednesday, November 28, 2012 10:26 AM
To: pgsql-general@postgresql.org
Subject: Re: pg_listening_channels()

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Igor Neyman asked:

[Postgres 9.2]
> How (if it's even possible) can I get listening channels
> for all sessions/ connections in the database?

No, this is not possible. You can only see items
from your own session.

- --

IMHO, that's a step back comparing to "older" implementation of LISTEN/NOTIFY.

Of course allowing "payload" to be attached to NOTIFY signal (instead of being 
passed through some user defined table) is a good thing.
But, I (and probably many others) use LISTEN/NOTIFY mechanism to notify client 
program connected to postgres database about changes made in the database
Implementation prior to PG 9.0:

INTERESTED_CLIENT: LISTEN my_alert;
SOME_OTHER_PROGRAM: INSERTS/UPDATES/DELETES table_client_interested_in;
ON INSERT/UPDATE/DELETE TRIGGER: check if anyone listens on "my_alert" channel 
by querying pg_listener table, sends NOTIFY my_alert signal and inserts message 
into user_message_table;
INTERESTED_CLIENT: upon receiving NOTIFY signal reads message from  
user_message_table;

With PG 9.0 changes I lost ability to check if anyone is interested in the 
NOTIFY signal and payload I'm about to send.
Seems like this change was not thought through completely.

Regards,
Igor Neyman


-- 
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] 'alternatives'

[Andres Freund]

Hi,

On 2012-11-28 14:16:18 -0800, Christophe Pettus wrote:
> Thanks!

Not much to thank for, the answer was actually wrong...

> Does that apply to views as well?  (This particular plan was not from a 
> prepared or PL/pgSQL statement, but did include views.)

Its not really relevant for views no.

The real answer for this is that this actually a 8.4 feature not the
aforementioned 9.2 feature.

The commit introducing this is:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bd3daddaf232d95b0c9ba6f99b0170a0147dd8af

What that does to add hashing support for IN(). But hashing can be
pessimal in comparison to a explicit check if only a few values come in,
so this can be checked at runtime after the above commit...

Greetings,

Andres Freund


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

[GENERAL] Updateable VIEWS and the manual

[Thomas Kellerer]

Hi,

I just noticed the following statement in the CREATE VIEW documentation (9.2 
and 9.1):

Currently, views are read only: the system will not allow an insert,
update, or delete on a view. You can get the effect of an updatable view by
creating rules that rewrite inserts, etc. on the view into appropriate 
actions
on other tables. For more information see CREATE RULE.

Wouldn't it make more sense to refer to INSTEAD OF triggers?

My understanding was that an INSTEAD OF trigger was preferred over a rule 
nowadays.

Regards
Thomas



--
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] 'alternatives'

[Tom Lane]

Andres Freund  writes:
> The commit introducing this is:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bd3daddaf232d95b0c9ba6f99b0170a0147dd8af
> What that does to add hashing support for IN(). But hashing can be
> pessimal in comparison to a explicit check if only a few values come in,
> so this can be checked at runtime after the above commit...

Yeah.  If you look at the subplans, one is designed for retail probes
and the other is designed for sucking up the entire subquery result into
a hashtable.  EXPLAIN ANALYZE will show you that only one gets used at
runtime.  (The idea of dynamic switchover hasn't gotten anywhere yet.)

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] pg_listening_channels()

[Tom Lane]

Igor Neyman  writes:
> With PG 9.0 changes I lost ability to check if anyone is interested in the 
> NOTIFY signal and payload I'm about to send.
> Seems like this change was not thought through completely.

[ shrug... ] It was debated extensively and the advantages of the new
implementation were deemed to outweigh the disadvantages.

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] create table in memory

[Seref Arikan]

Thanks Merlin,
I'll take a better look at CTE.

Best regards
Seref


On Tue, Nov 27, 2012 at 4:48 PM, Merlin Moncure  wrote:

> On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan 
> wrote:
> > I have a function that creates a temp table, inserts rows into it,
> performs
> > joins, and returns a single integer as a result. This is pg 9.1. All
> > sessions are using the exact same temp table structure.
> > re performance requirements: I need this function to return as fast as
> > possible :) On a production server, if the function can complete in
> around
> > 10-20 milliseconds, it would be really good (below 10 ms would be great).
> > The average number of inserted into temp table is around 800, and there
> are
> > about 10 columns in the current design.
>
> Well, one thing to explore is use of CTE.  general structure is:
> WITH temp_data AS
> (
>   SELECT a_bunch_of_stuff ..
> ),
> modify_something AS
> (
>   UPDATE something_else
>   FROM temp_data ...
>   RETURNING *
> )
> SELECT result_code
> FROM modify_something ...;
>
> There are some pros and cons with this approach vs classic temp table
> generation.
> Pros:
> *) since 9.1 and 'data modifying with' feature, you are not very much
> constrained
> *) dispense with traditional headaches in terms of managing temp tables
> *) very neat and clean
> Cons:
> *) can't build indexes
>
> A hybrid approach, which is more complicated, is to organize a
> permanent table with the current transaction id (via
> txid_current()::text) as the left most part of the primary key.
>
> CREATE TABLE transaction_data
> (
>   xid text default txid_current()::text,
>   keyfield1 int,
>   keyfield2 text,
>   [data fields]
> );
>
> This is really fast since the data/indexes are ready to go at all
> time.  Your function always inserts, cleanup of stale transaction
> records we can dispense to background process, particularly if you can
> find appropriate time to TRUNCATE the table (which would hiccup
> processes using the table).
>
> merlin
>

Re: [GENERAL] Updateable VIEWS and the manual

[Merlin Moncure]

On Wed, Nov 28, 2012 at 4:43 PM, Thomas Kellerer  wrote:
> Hi,
>
> I just noticed the following statement in the CREATE VIEW documentation (9.2
> and 9.1):
>
> Currently, views are read only: the system will not allow an insert,
> update, or delete on a view. You can get the effect of an updatable view
> by
> creating rules that rewrite inserts, etc. on the view into appropriate
> actions
> on other tables. For more information see CREATE RULE.
>
> Wouldn't it make more sense to refer to INSTEAD OF triggers?
>
> My understanding was that an INSTEAD OF trigger was preferred over a rule
> nowadays.

yup.

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] Chang wal block size and database block size in Windows

[classical_89]

Hi everyone , I work with PostgreSQL on Windows XP . I want to ask you
something .How can i change Postgres wal block size and database block size
in Windows ?.I had tried search in postgresql.conf but seem these params not
list in this file , i think it would be place at somewhere 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Chang-wal-block-size-and-database-block-size-in-Windows-tp5734027.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] Chang wal block size and database block size in Windows

[Tom Lane]

classical_89  writes:
> Hi everyone , I work with PostgreSQL on Windows XP . I want to ask you
> something .How can i change Postgres wal block size and database block size
> in Windows ?

You'd need to recompile from source, and tell the configure script
which block size you want.  (It looks like the MSVC build scripts
know how to set this, but I have no idea exactly how to set the
blocksize option with them.)

By and large, people haven't found that adjusting these values buys
a whole lot, which is why we've never bothered to make them adjustable
short of source-code changes.

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

[GENERAL] Is there a working pgsql-fdw for PostgreSQL 9.2?

[wd]

Only found one
http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html working
for PostgreSQL 9.1.