Re: pg_restore question (-T and -t)

Why not just try it! Answer: all -t switches after the first one are
ignored. (And, no, "pg_restore --help" does not mention that). However
with -l and -L, you have a much more powerful mechanism for specifying
exactly which objects you want restored.

> -----Original Message-----
> From: pgsql-general-owner <at> postgresql.org [mailto:pgsql-general-
> owner <at> postgresql.org] On Behalf Of Tony Caduto
> Sent: Wednesday, December 31, 2008 1:50 PM
> To: pgsql general
> Subject: [GENERAL] pg_restore question (-T and -t)
> 
> Hi,
> does anyone know if you can do multiple
> -T or -t   (restore named trigger, restore name table) switches?
> 
> In the docs for pg_restore it does not specify if it will accept more
> than one, but in the pg_dump docs the -n and -t switches allow
> multiples.
> 
> Thanks,
> 
> tony

--

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

Re: pg_restore question (-T and -t)

manage changes to views having depencies

Can anyone recommend a reasonably efficient system for changing a view
definition (say by adding a column) when it has a bunch of dependent
functions?

Right now I work with the output from pg_dump to recreate things after
doing "DROP VIEW ... CASCADE".  But the pg_dump schema output is only
approximately sorted by dependencies, and "create table..." is
sprinkled all through it.  That means I have to carefully comb through
and select the pieces I need.

Is there a way to just script the view definitions, then the
functions?  Or maybe I'm looking at it the wrong way?

--

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

Re: manage changes to views having depencies

On Wed, Dec 31, 2008 at 9:57 PM, Eric Worden <worden.eric <at> gmail.com> wrote:
> Can anyone recommend a reasonably efficient system for changing a view
> definition (say by adding a column) when it has a bunch of dependent
> functions?
>
> Right now I work with the output from pg_dump to recreate things after
> doing "DROP VIEW ... CASCADE".  But the pg_dump schema output is only
> approximately sorted by dependencies, and "create table..." is
> sprinkled all through it.  That means I have to carefully comb through
> and select the pieces I need.
>
> Is there a way to just script the view definitions, then the
> functions?  Or maybe I'm looking at it the wrong way?

Last place we worked we had a little plpgsql script to drop all views.
 How much and what kind of control you wanna build for that is up to
you.  My guess is that this is an operation done during downtime, so
as not to impact users and allow for the code changes in the
application to be updated too.  Or at least preceding it, and so you
can just dump all views and recreate them.

Then just have all your view create script ready to run.  I'd keep
that as the standard, not what comes out of pg_dump's schema output.
Changes don't go into the test database or higher without going
through the view creation process.

--

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

Re: postgres block_size problem

Bhujbal, Santosh wrote:

> 2008-12-30 14:57:33 IST  DETAIL:  The database cluster was initialized
> with BLCKSZ 8192, but the server was compiled with BLCKSZ 16384.
> 
> 2008-12-30 14:57:33 IST  HINT:  It looks like you need to recompile or
> initdb.

This error message tells you the answer. You can't run PostgreSQL with a
data directory that was initialized with a different block size. You
will need to dump and reload the database.

You really shouldn't be hitting the 8k row size limit anyway. PostgreSQL
uses out-of-line TOAST storage for large values, so the only way you're
likely to be hitting it is by having absurd numbers of small fields in
your tables (or maybe a custom data type for which you've not
implemented TOAST support?). If you have that many fields in your tables
it might be time to look at your schema design.

--
Craig Ringer

--

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

How to cast a general record?

Hello,

I want to log with triggers or in functions, and these logs should be
independet of the transaction. Beside i want to have the information
which action was commited and which not.

So my idea was to log into the same database with dblink, return the
primary keys and add them into a commit table.

But my problem is, that I do not now how to write the rule properly.

My schema locks like this:

CREATE TABLE log_msg (
  msg_id bigserial not null,
  msg text not null,
  constraint msg_pkey primary key (msg_id)
);

CREATE TABLE log_commit (
  msg_id bigint not null,
  constraint msg_pkey primary key (msg_id)
);

CREATE VIEW log AS
SELECT log_msg.*, log_commit.msg_id IS NOT NULL AS commited
FROM log_msg LEFT JOIN log_commit USING (msg_id);

CREATE OR REPLACE RULE "insert_log" AS
  ON INSERT TO log DO INSTEAD

 -- now this is pseudo code:
INSERT INTO log_commit (msg_id)
SELECT dblink('dbname=samedb', 'INSERT log_msg (msg) VALUES (' 
  || quote_literal(new.msg)
  || ') RETURNING msg_id');

Regards,
  Gerhard

Re: How to cast a general record?

Re: encoding of PostgreSQL messages

On Wednesday 31 December 2008 20:23:47 Tom Lane wrote:
> > The proper fix is probably to include the client encoding in the
> > connection startup message.
>
> What of errors occurring before such an option could be applied?

Connection errors are handled by the client, which knows the client encoding.  
If the setting of the client encoding would be one of the first things to be 
done on the server side, you would only have a handful of possible error 
conditions left (e.g., setlocale failed, out of memory).  You could choose to 
report those in plain ASCII or send a special error code that the client can 
resolve.  Although I guess no one could fault us if "could not set language" 
is reported not translated. 

> I think that ultimately it's necessary to accept that there will be some
> window during connection startup where sending plain ASCII (English)
> messages is the best recourse.

Ultimately yes.  But we currently handle the client encoding quite late in the 
startup sequence so that many connection startup failure messages that are of 
interest to normal users would likely be affected.  So moving the client 
encoding handling to the earliest possible phase would still be desirable.

--

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

Re: manage changes to views having depencies

Eric Worden wrote:
> Can anyone recommend a reasonably efficient system for changing a view
> definition (say by adding a column) when it has a bunch of dependent
> functions?
> 
> Right now I work with the output from pg_dump to recreate things after
> doing "DROP VIEW ... CASCADE".  But the pg_dump schema output is only
> approximately sorted by dependencies, and "create table..." is
> sprinkled all through it.  That means I have to carefully comb through
> and select the pieces I need.
> 
> Is there a way to just script the view definitions, then the
> functions?  Or maybe I'm looking at it the wrong way?
> 

I've run into the situation similar to yours but with views and 
foreign key dependent on tables. Same process probably would work 
for you.

I use pgAdmin III to run my desired change script, which might 
include a DELETE...CASCADE or ALTER ... command(s), wrapped 
inside a BEGIN ... ROLLBACK block (since this may require a 
number of iterations). The messages in the pgAdmin output pane 
identify dependencies specifically related to the changes you 
intend to make.

Then for each dependency, I build out my script by inserting it 
in between the DROP ... CREATE commands identified for each 
dependency in each iteration of the process until all the 
dependencies are resolved.

This is not necessarily what I would think of as ideal, since it 
is not really very well automated to the extent I'd like to see, 
but it has worked, and it has proven "efficient enough" for a 
process that is not a routine, everyday task.

--

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

Re: How to cast a general record?

Gerhard Heift wrote:
> I want to log with triggers or in functions, and these logs should be
> independet of the transaction. Beside i want to have the information
> which action was commited and which not.
> 
> So my idea was to log into the same database with dblink, return the
> primary keys and add them into a commit table.
> 
> But my problem is, that I do not now how to write the rule properly.

The part of your plan where I see the biggest problem is the wish
to log whether the transaction was committed or not. You will probably
not be able to do that.

> CREATE OR REPLACE RULE "insert_log" AS ON INSERT TO log DO INSTEAD

... and shouldn't it be "DO ALSO"?

Yours,
Laurenz Albe

--

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