Re: [GENERAL] Question(s) about crosstab

[Scott Marlowe]

On Tue, Dec 17, 2013 at 10:42 PM, Ken Tanzer  wrote:
>
> On Tue, Dec 17, 2013 at 5:11 PM, David Johnston  wrote:
>>
>> 3) Limitation of SQL - explained below:
>>
>> The function call string that you pass in is just that, a string, the SQL
>> construct within which it resides has no knowledge of its contents.
>>
>> SQL has the hard requirement that at the time you submit a query all columns
>> must be known.  If a function is polymorphic (in the sense it can output
>> different columns/row-types) then when you call that function you must
>> indicate which columns (and types) are going to be output by the function
>> during this specific execution.
>
>
> I guess crosstabs were not all that I hoped they were (basically pivot 
> tables), but thanks for the clear explanation.

In the past I've written simple bash, perl, php etc scripts that
interrogated catalogs and then built my crosstab queries for me. You
could do it in a pl language, tho probably not easily in plpgsql.
plpython or plperl etc would proabably be a good place to start.


-- 
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] Question(s) about crosstab

[Ken Tanzer]

On Tue, Dec 17, 2013 at 5:11 PM, David Johnston  wrote:

> 3) Limitation of SQL - explained below:
>
> The function call string that you pass in is just that, a string, the SQL
> construct within which it resides has no knowledge of its contents.
>
> SQL has the hard requirement that at the time you submit a query all
> columns
> must be known.  If a function is polymorphic (in the sense it can output
> different columns/row-types) then when you call that function you must
> indicate which columns (and types) are going to be output by the function
> during this specific execution.
>

I guess crosstabs were not all that I hoped they were (basically pivot
tables), but thanks for the clear explanation.

Cheers,
Ken

-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ *
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Question(s) about crosstab

[David Johnston]

Ken Tanzer wrote
> 1)  Is there a simpler way?  I'm hoping I made this unnecessarily
> cumbersome and complicated.
> 2)  AFAICT, if a new unit type were to be added, I'd have to rewrite this
> query.  Is there any way to avoid that?
> 3)  It seems like everything after the first query, except for the
> category
> field, is redundant information, and that in theory you should be able to
> say crosstab('query','category_field').  Is there any inherent reason this
> simpler form couldn't work, or is it just that no one has wanted to do it,
> or gotten to it yet?

1) Pivot Tables...(not a PostgreSQL feature I'm afraid)

2) Not that I am aware of.  I would suggest writing the query so that
"Other" is a valid group and any unmapped types get aliased to "Other" so at
least the query counts everything and you know that if "Other" is non-zero
you have some alterations to make.

3) Limitation of SQL - explained below:

The function call string that you pass in is just that, a string, the SQL
construct within which it resides has no knowledge of its contents.

SQL has the hard requirement that at the time you submit a query all columns
must be known.  If a function is polymorphic (in the sense it can output
different columns/row-types) then when you call that function you must
indicate which columns (and types) are going to be output by the function
during this specific execution.

As an aside you should consider dollar-quoting:

SELECT function_call(
$arg1$ SELECT '1' AS one $arg1$, $arg2$ SELECT '2two' AS "22" $arg2"
);

That way you do not need to deal with escaping the embedded quotes in the
query and can fairly easily extract the query text and run it standalone,
modify it, then copy it back without modification.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Question-s-about-crosstab-tp5783810p5783818.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] Question(s) about crosstab

[Ken Tanzer]

On Tue, Dec 17, 2013 at 3:47 PM, rob stone  wrote:


> SELECT UH.grant_number_code, UH.housing_project_code, UH. count,
> UT.description
> FROM l_unit_type UT, unit_hold UH
> WHERE UH.unit_type_code = UT.unit_type_code;
>
>
> Easier to create a view.
>
>
Thanks Rob, but that doesn't get the data into a 1 line per grant/building
format, with the unit types as columns.  That's why I was looking at
crosstab in the first place!

Cheers,
Ken

-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ *
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Question(s) about crosstab

[rob stone]

O
n Tue, 2013-12-17 at 15:31 -0800, Ken Tanzer wrote:


> 
> \d unit_hold
>  Table "public.unit_hold"
> Column| Type  | Modifiers 
> --+---+---
>  grant_number_code| character varying(10) | 
>  housing_project_code | character varying(10) | 
>  unit_type_code   | character varying(10) | 
>  count| bigint| 
> 
> 
> SELECT * FROM unit_hold limit 3;
>  grant_number_code | housing_project_code | unit_type_code | count 
> ---+--++---
>  1 |  | 4BR| 1
>  1 |  | 1BR| 1
>  1 |  | 1BR| 1
> 
> 
> SELECT unit_type_code,description FROM l_unit_type;
>  unit_type_code | description 
> +-
>  5BR| 5 Bedroom
>  4BR| 4 Bedroom
>  3BR| 3 Bedroom
>  6BR| 6 Bedroom
>  UNKNOWN| Unknown
>  GROUP  | Group Home
>  2BR| 2 Bedroom
>  1BR| 1 Bedroom
>  0BR| Studio
>  SRO| SRO
> 
> 
> 
> 
> I thought this would be a good candidate for crosstab.  After
> wrestling with the documentation, this is the best I could come up
> with:
> 
> 
> SELECT * FROM crosstab(
>   'SELECT housing_project_code||''_''||grant_number_code AS
> project_and_grant,grant_number_code,housing_project_code,unit_type_code,count 
> FROM unit_hold ORDER BY 1,2',
>   'SELECT * FROM (SELECT DISTINCT unit_type_code FROM l_unit_type) foo
> ORDER BY unit_type_code ~ ''^[0-9]'' DESC, unit_type_code'
> ) AS ct(project_and_grant varchar, grant_number_code varchar,
> housing_project_code varchar, "0BR" bigint, "1BR" bigint, "2BR"
> bigint, "3BR" bigint, "4BR" bigint, "5BR" bigint, "6BR" bigint,"GROUP"
> bigint, "SRO" bigint, "UNKNOWN" bigint)
> 
> 
> 
> So here are my questions:
> 
> 
> 1)  Is there a simpler way?  I'm hoping I made this unnecessarily
> cumbersome and complicated.
> 2)  AFAICT, if a new unit type were to be added, I'd have to rewrite
> this query.  Is there any way to avoid that?
> 3)  It seems like everything after the first query, except for the
> category field, is redundant information, and that in theory you
> should be able to say crosstab('query','category_field').  Is there
> any inherent reason this simpler form couldn't work, or is it just
> that no one has wanted to do it, or gotten to it yet?
> 
> 
> Thanks in advance!
> 
> 
> Ken
> 
> 
> 
> 
> -- 
> 
> AGENCY Software  
> A data system that puts you in control
> 100% Free Software
> http://agency-software.org/
> ken.tan...@agency-software.org
> (253) 245-3801
> 
> 
> Subscribe to the mailing list to
> learn more about AGENCY or
> follow the discussion.


SELECT UH.grant_number_code, UH.housing_project_code, UH. count,
UT.description
FROM l_unit_type UT, unit_hold UH
WHERE UH.unit_type_code = UT.unit_type_code;

 
Easier to create a view.



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

[GENERAL] Question(s) about crosstab

[Ken Tanzer]

Hi.  I've got a simple table unit_hold, with grant numbers, buildings and
counts of unit types, which I need to summarize, along with a table listing
unit types:

\d unit_hold
 Table "public.unit_hold"
Column| Type  | Modifiers
--+---+---
 grant_number_code| character varying(10) |
 housing_project_code | character varying(10) |
 unit_type_code   | character varying(10) |
 count| bigint|

SELECT * FROM unit_hold limit 3;
 grant_number_code | housing_project_code | unit_type_code | count
---+--++---
 1 |  | 4BR| 1
 1 |  | 1BR| 1
 1 |  | 1BR| 1

SELECT unit_type_code,description FROM l_unit_type;
 unit_type_code | description
+-
 5BR| 5 Bedroom
 4BR| 4 Bedroom
 3BR| 3 Bedroom
 6BR| 6 Bedroom
 UNKNOWN| Unknown
 GROUP  | Group Home
 2BR| 2 Bedroom
 1BR| 1 Bedroom
 0BR| Studio
 SRO| SRO


I thought this would be a good candidate for crosstab.  After wrestling
with the documentation, this is the best I could come up with:

SELECT * FROM crosstab(
  'SELECT housing_project_code||''_''||grant_number_code AS
project_and_grant,grant_number_code,housing_project_code,unit_type_code,count
FROM unit_hold ORDER BY 1,2',
  'SELECT * FROM (SELECT DISTINCT unit_type_code FROM l_unit_type) foo
ORDER BY unit_type_code ~ ''^[0-9]'' DESC, unit_type_code'
) AS ct(project_and_grant varchar, grant_number_code varchar,
housing_project_code varchar, "0BR" bigint, "1BR" bigint, "2BR" bigint,
"3BR" bigint, "4BR" bigint, "5BR" bigint, "6BR" bigint,"GROUP" bigint,
"SRO" bigint, "UNKNOWN" bigint)

So here are my questions:

1)  Is there a simpler way?  I'm hoping I made this unnecessarily
cumbersome and complicated.
2)  AFAICT, if a new unit type were to be added, I'd have to rewrite this
query.  Is there any way to avoid that?
3)  It seems like everything after the first query, except for the category
field, is redundant information, and that in theory you should be able to
say crosstab('query','category_field').  Is there any inherent reason this
simpler form couldn't work, or is it just that no one has wanted to do it,
or gotten to it yet?

Thanks in advance!

Ken


-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ *
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] pg_stat_tmp

[Jeff Janes]

On Tue, Dec 17, 2013 at 3:25 AM, Tim Kane  wrote:

>
> Thanks Jeff, Magnus
>
> Thanks for the suggestions.
> This morning the same issue occurred, but this time it also complained
> that it failed to write to pg_clog  (previous days, the only failure was in
> writing to pg_stat_tmp)
>
>
...


> 2013-12-17 07:13:08 GMT LOG:  could not write temporary statistics file 
> "pg_stat_tmp/pgstat.tmp":
> No space left on dev2013-12-17 07:25:15 GMT WARNING:  terminating
> connection because of crash of another server
>  process
>

 ...


>
>
> It’s never failed to write pg_xlog’s though – or at least, never reported
> a failure.
>

If your log file is on the same partition as all the other stuff, you are
probably losing log messages because there is no room to log them.  The
mangled line above tends to support this.  Logging stopped for 12 minutes,
until the crash freed up some space so it could resume.



>
> For now, I’ve altered the pg_stat_tmp path and we’ll see what happens
> tomorrow – Ideally though, yes.. I’d like to see this happening at runtime
> and get a better picture of what’s blowing out here.
>
>
>
> Further to this however, I notice that the base directory is consuming
> more disk than I would have expected it to (all our relations are stored in
> tablespaces assigned to other disks).
> Looking for the larger culprits, I note the following files consuming 4.4GB
>
> 9.2/main/base/74641/2260957
> 9.2/main/base/74641/2260957.1
> 9.2/main/base/74641/2260957.2
> 9.2/main/base/74641/2260957.3
> 9.2/main/base/74641/2260957.4
>
> I notice the timestamps are just shy of the time we experience that
> pg_stat_tmp issue.
>
> -rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957
> -rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.1
> -rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.2
> -rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.3
> -rw--- 1 postgres postgres  328466432 Dec 17 06:56 2260957.4
>
>
> They appear to be orphaned oid’s, though I’m not sure this is definitive:
>
> clone=# select pg_relation_filepath(2260957);
>

The transaction that was making the table probably never committed, so its
entry never becomes visible.  I don't know of any good way of finding out
what the name of an uncommitted object will eventually be, which is rather
annoying when trying to monitor massives loads that happen inside a large
transaction.

(And what you should really be doing is select relname from pg_class where
relfilenode=2260957,  the relfilenode is often the same as the oid, but not
always.)

Cheers,

Jeff

Re: [GENERAL] Need Help Restoring Old Backup

[Adrian Klaver]

On 12/15/2013 11:00 AM, Steve Knott wrote:

Hi,

I have a really old database (5+ years old) that was backed up stored
away for a while.  Now I want some data out of it.


Not sure what you mean here?  Do you mean you stored the database data 
directory or did a dump and stored that?




I originally thought that if I made a backup and kept around the
original database install.exe file, I could reinstall the db & restore
the data.  It looks like that is not happening.  The old database was
somewhere in 8.2.x before it was shut down.

Whenever I try to restore, I get:
 > pg_restore: [archiver (db)] Error while PROCESSING TOC:
 > pg_restore: [archiver (db)] Error from TOC entry 1490: 1259 26787
TABLE users client
 > ...
 > pg_restore: [custom archiver] found unexpected block ID (1257691431)
when reading data -- expected 2049


So here you are using an old dump file produced with -Fc switch?

What version of pg_restore are you using?

A dump/restore should work across architectures, but for troubleshooting 
purposes have you changed machines?




I tried restoring on an 8.2, 8.4 and 9.0, but each fail with similar
error messages.


How similar, the same table, the same unexpected block id message, or other?




Is there anyway to repair this file or break it up some I can get most
of the data out of it?  It appears to be stopping half way through.  If
I could get it past this point, I could at least reclaim the tables
after this issue.



Well you could use the below to selectively dump items.

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

-l
--list

List the contents of the archive. The output of this operation can 
be used as input to the -L option. Note that if filtering switches such 
as -n or -t are used with -l, they will restrict the items listed.


-L list-file
--use-list=list-file

Restore only those archive elements that are listed in list-file, 
and restore them in the order they appear in the file. Note that if 
filtering switches such as -n or -t are used with -L, they will further 
restrict the items restored.


list-file is normally created by editing the output of a previous 
-l operation. Lines can be moved or removed, and can also be commented 
out by placing a semicolon (;) at the start of the line. See below for 
examples.





Thanks for any help,
Steve




--
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] Postgres SQL 8.4 regularly craches

[Scott Marlowe]

On Mon, Dec 16, 2013 at 3:24 AM, Hungerbühler Philipp (hune)
 wrote:
>
> Hello all
>
>
>
> We have a postgres server on Windows 2008 R2 used by tree services. This 
> worked quite for a long time (several years) but lately the Postgres service 
> regularly crashes
>
>
>
> 2013-12-11 14:03:10 CET LOG:  Serverprozess (PID 3576) wurde durch Ausnahme 
> 0xC043 beendet (sorry German)
>
>
>
> I researched and found that this exception is related to:
>
>
>
> 0xC043
> STATUS_SHARING_VIOLATION
>
> A file cannot be opened because the share access flags are incompatible.

Any chance you're running anti-virus on that machine? If you can set
it to exclude the postgresql directory, or turn it off, you can see if
the problem then goes away.


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

[GENERAL] Postgres SQL 8.4 regularly craches

[hune]

Hello all

We have a postgres server on Windows 2008 R2 used by tree services. This worked 
quite for a long time (several years) but lately the Postgres service regularly 
crashes

2013-12-11 14:03:10 CET LOG:  Serverprozess (PID 3576) wurde durch Ausnahme 
0xC043 beendet (sorry German)

I researched and found that this exception is related to:

0xC043
STATUS_SHARING_VIOLATION

A file cannot be opened because the share access flags are incompatible.


I also found a Microsoft fix (http://support.microsoft.com/kb/2707576/en-us) 
related to this error, but the fix does not apply to our system. I defragmented 
the drive were the Postgres data resides. But still the service crashes all 
several days.

Any idea how I could fix this or how I could find out, why the service is 
crashing? It does not seem to be related to the application using the Postgres 
DBs, because there seem to be no requests to Postgres exactly at the time it 
crashes.

Any help would be appreciated. For the moment an upgrade to Postgres 9 is not 
possibly, because the applications using Postgres DBs are quite vital.

Regards
Philipp
_

ZHAW Zürcher Hochschule für Angewandte Wissenschaften
ICT, Server & Systeme

Philipp Hungerbühler
Gertrudstrasse 15
CH-8401 Winterthur

Tel:   +41 58 934 69 44
E-Mail:  philipp.hungerbueh...@zhaw.ch
_

[GENERAL] Bus error in libxml2 in postgresql 9.2.x on FreeBSD

[Steve McCoy]

 Hello,

I found some semi-recent messages related to this in 9.0.x, but wanted to
confirm for anybody searching that the same issue exists in 9.2.x. It
crashes for me with a similar backtrace as [Laurentius Purba’s][1]. The
same fix/workaround applies as well: Compile libxml2 without the “threads”
option.

If I’m understanding the explanations correctly, the issue is that
libxml2+threads expects something to be initialized by the calling program
(postgres), but since the program isn’t multithreaded, this doesn’t happen.
It sounds like something that neither side can really fix, but if I’m wrong
and any devs would like a core file, I can provide one.


[1]
http://www.postgresql.org/message-id/camflbnhq6641ymbij2-7ozksgmgfne5v3ggfkvyxojy8umr...@mail.gmail.com

[GENERAL] help replacing expresion in plpgsql

[Juan Pablo L]

Hi, i have a function that receives a parameter which represents days:

FUNCTION aaa_recharge_account(expdays integer)

i want to add those days to the CURRENT_DATE, but i do not know how to do
it, i have tried several ways to replace that in an expresion like:

newexpdate := CURRENT_TIMESTAMP + interval '$1 days' using expdays;
(newexpdate is declared as timestamp)

and many more but none work, can someone please help me to find out how can
i replace that parameter into an expression that i can add to
CURRENT_TIMESTAMP or any other way that i can accomplish what i need which
is to add that parameter to the current timestamp. thanks!!!

[GENERAL] Need Help Restoring Old Backup

[Steve Knott]

Hi,

I have a really old database (5+ years old) that was backed up stored 
away for a while.  Now I want some data out of it.


I originally thought that if I made a backup and kept around the 
original database install.exe file, I could reinstall the db & restore 
the data.  It looks like that is not happening.  The old database was 
somewhere in 8.2.x before it was shut down.


Whenever I try to restore, I get:
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1490: 1259 26787 
TABLE users client

> ...
> pg_restore: [custom archiver] found unexpected block ID (1257691431) 
when reading data -- expected 2049


I tried restoring on an 8.2, 8.4 and 9.0, but each fail with similar 
error messages.


Is there anyway to repair this file or break it up some I can get most 
of the data out of it?  It appears to be stopping half way through.  If 
I could get it past this point, I could at least reclaim the tables 
after this issue.


Thanks for any help,
Steve








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

Re: [DOCS] Re: postgresql.org inconsistent (Re: [GENERAL] PG replication across DataCenters)

[Wolfgang Keller]

> It looks like it's been morphed into TED, the TransLattice Elastic
> Database.  From their FAQ[1]:
> 
> TransLattice Elastic Database (TED)
> 
> What’s the basis of TED? Did you write it from scratch?
> 
> We started TED from PostgreSQL, a very robust, open-source,
> ACID-compliant, fully transactional RDBMS and Postgres-R, a PostgreSQL
> extension that provides efficient, fast and consistent database
> replication . Extensive engineering enhancements allows TED to
> maintain ACID semantic transactions while operating in a
> geographically distributed cluster.
> 
> [1] http://www.translattice.com/faq.shtml

I could not find any valid technical information on that site that would
give details about what version of PostgreSQL they are based on etc.
And if they have anything more recent than postgres-r.org, their
product doesn't appear to be open source, since I couldn't find
anything to download there.

In fact their entire site reads to me just like the usual
salespromotionspeak nonsense written for pointy-haired morons.

Excuse me, but I can't take those people really serious.

If Bettina Kemme is working with them, she should at least make sure to
get them a reasonably useful WWW site.

Sincerely,

Wolfgang


-- 
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] [SQL] [ADMIN] Scheduled Events

[Marcin Krawczyk]

I'm using pgAgent you mentioned before. You can easily schedule recurring
tasks from within pgAdmin.

regards
mk


2013/12/11 Dev Kumkar 

> Resending...
>
> On Wed, Dec 11, 2013 at 8:29 PM, Dev Kumkar wrote:
>
>> Yes actually that's one alternate solution to use cron or windows
>> scheduled tasks.
>>
>> The intent is to call certain stored procedures at certain time intervals.
>>
>>
>> On Wed, Dec 11, 2013 at 7:24 PM, Payal Singh  wrote:
>>
>>> You can schedule those jobs in a crontab. If it is a SQL command, you
>>> can use  the -c option with psql.
>>>
>>>
>>> On Wed, Dec 11, 2013 at 05:08:12PM +0530, Dev Kumkar wrote:
>>> > How to create scheduled events in postgres simillar to whats event in
>>> > Sybase.
>>> > Is there any method of doing so?
>>> >
>>> > Also am looking at PgAgent which can create jobs but is it similar like
>>> > events in sybase.
>>> >
>>> > Please suggest.
>>> >
>>> > Regards...
>>>
>>

Re: [GENERAL] pg_stat_tmp

[Tim Kane]

Thanks Jeff, Magnus

Thanks for the suggestions.
This morning the same issue occurred, but this time it also complained that
it failed to write to pg_clog  (previous days, the only failure was in
writing to pg_stat_tmp)


2013-12-17 07:13:04 GMT DETAIL:  Could not write to file "pg_clog/0004" at
offset 212992: No space left on device.
2013-12-17 07:13:05 GMT ERROR:  could not access status of transaction 0
2013-12-17 07:13:05 GMT DETAIL:  Could not write to file "pg_clog/0004" at
offset 212992: No space left on device.
2013-12-17 07:13:06 GMT ERROR:  could not access status of transaction 0
2013-12-17 07:13:06 GMT DETAIL:  Could not write to file "pg_clog/0004" at
offset 212992: No space left on device.
2013-12-17 07:13:07 GMT LOG:  could not write temporary statistics file
"pg_stat_tmp/pgstat.tmp": No space left on device
2013-12-17 07:13:07 GMT LOG:  could not write temporary statistics file
"pg_stat_tmp/pgstat.tmp": No space left on device
2013-12-17 07:13:08 GMT LOG:  could not write temporary statistics file
"pg_stat_tmp/pgstat.tmp": No space left on device
2013-12-17 07:13:08 GMT LOG:  could not write temporary statistics file
"pg_stat_tmp/pgstat.tmp": No space left on dev2013-12-17 07:25:15 GMT
WARNING:  terminating connection because of crash of another server
 process
2013-12-17 07:25:15 GMT 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 m
emory.
2013-12-17 07:25:15 GMT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2013-12-17 07:25:16 GMT LOG:  all server processes terminated;
reinitializing
2013-12-17 07:25:17 GMT LOG:  database system was interrupted; last known up
at 2013-12-17 07:08:22 GMT
2013-12-17 07:25:17 GMT LOG:  database system was not properly shut down;
automatic recovery in progress
2013-12-17 07:25:17 GMT LOG:  redo starts at 5BC/CF3F9888
2013-12-17 07:25:18 GMT LOG:  could not open file
"pg_xlog/000105BC00D6" (log file 1468, segment 214): No such
file or directory
2013-12-17 07:25:18 GMT LOG:  redo done at 5BC/D5FFFEC0
2013-12-17 07:25:18 GMT LOG:  last completed transaction was at log time
2013-12-17 07:25:12.989653+00
2013-12-17 07:25:19 GMT LOG:  database system is ready to accept connections
2013-12-17 07:25:19 GMT LOG:  autovacuum launcher started



It’s never failed to write pg_xlog’s though – or at least, never reported a
failure.

For now, I’ve altered the pg_stat_tmp path and we’ll see what happens
tomorrow – Ideally though, yes.. I’d like to see this happening at runtime
and get a better picture of what’s blowing out here.



Further to this however, I notice that the base directory is consuming more
disk than I would have expected it to (all our relations are stored in
tablespaces assigned to other disks).
Looking for the larger culprits, I note the following files consuming 4.4GB

9.2/main/base/74641/2260957
9.2/main/base/74641/2260957.1
9.2/main/base/74641/2260957.2
9.2/main/base/74641/2260957.3
9.2/main/base/74641/2260957.4

I notice the timestamps are just shy of the time we experience that
pg_stat_tmp issue.

-rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957
-rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.1
-rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.2
-rw--- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.3
-rw--- 1 postgres postgres  328466432 Dec 17 06:56 2260957.4


They appear to be orphaned oid’s, though I’m not sure this is definitive:

clone=# select pg_relation_filepath(2260957);
 pg_relation_filepath
--

(1 row)



I probably would have thought these to be temporary files – except for that
I have temp_tablespace set elsewhere.

Are these truly orphaned files that postgresql no longer needs (possibly due
to a crash) ?Is there a mechanism to scan the PG_DATA directories to
locate such files?
FYI, we’re running 9.2.4


Thanks again,

Tim



From:  Jeff Janes 
Date:  Monday, 16 December 2013 17:32
To:  Tim Kane 
Cc:  pgsql-general General 
Subject:  Re: [GENERAL] pg_stat_tmp

On Mon, Dec 16, 2013 at 5:57 AM, Tim Kane  wrote:
> Hi all,
> 
> The past few days I’ve been encountering the following error, followed by a
> full db restart and recovery
> 
> 
> 2013-12-16 07:12:53 GMT LOG:  could not write temporary statistics file
> "pg_stat_tmp/pgstat.tmp": No space left on device

Is that the only thing in the logs?  pg_stat_tmp problems should not bring
down your database.  But problems with pg_xlog running out of space
certainly can--but they should also be logged.
 

> 
> This occurs at a time of moderate load, during the same set of operations each
> morning.
> Interestingly, when I execute this manually at any other time of date, the
> process completes normally.
> 
> I presume that the pg_stat_tmp location is system-wide and likely is not
> impacted by temp_tablespaces
> The root parti

Re: [GENERAL] How to rename the same column name in dozens of tables in Postgres?

[Stefan Schwarzer]

> Hi there,
> 
> I have dozens of tables where I need to rename one column in each of these. 
> Is there any generic way to do that?
> I am not really familiar with the scripting possibilities of Postgres.
> 
> Thanks a lot for your help!
> 
> Stefan
> 
> 
> You can do with script. I made one on fly for this, other's might have more 
> polished version. 
> 
> -bash-4.1$ more rename.sh
> #!/bin/bash
> OLDCOLUMN=aa
> NEWCOLUMN=a
> for i in $(psql -t -c "select table_schema||'.'||table_name from 
> information_schema.tables where table_schema='public';")
> do
>   /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i rename 
> column $OLDCOLUMN to $NEWCOLUMN;"
> done
> 
> Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your 
> requirement. 
> 
> One more way from command line
> 
> -bash-4.1$ OLDCOLUMN=xyz
> -bash-4.1$ NEWCOLUMN=abc
> -bash-4.1$ psql -c "select 'alter table '||table_schema||'.'||table_name||' 
> rename column $OLDCOLUMN to $NEWCOLUMN;' from information_schema.tables where 
> table_schema='public';" | psql

Thanks so much. I had to add the database user and database name:

psql -U XXX -c "select 'alter table '||table_schema||'.'||table_name||' rename 
column $OLDCOLUMN to $NEWCOLUMN;' from information_schema.tables where 
table_schema='gis';" my_database | psql -U XXX my_database

and still run in an error. But it renamed quite a bunch of the tables. So, very 
good!

Stef

Re: [GENERAL] How to rename the same column name in dozens of tables in Postgres?

[Raghavendra]

On Tue, Dec 17, 2013 at 2:23 PM, Raghavendra <
raghavendra@enterprisedb.com> wrote:

> On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer <
> stefan.schwar...@unep.org> wrote:
>
>> Hi there,
>>
>> I have dozens of tables where I need to rename one column in each of
>> these. Is there any generic way to do that?
>>
> I am not really familiar with the scripting possibilities of Postgres.
>>
>> Thanks a lot for your help!
>>
>> Stefan
>>
>>
> You can do with script. I made one on fly for this, other's might have
> more polished version.
>
> -bash-4.1$ more rename.sh
> #!/bin/bash
> OLDCOLUMN=aa
> NEWCOLUMN=a
> for i in $(psql -t -c "select table_schema||'.'||table_name from
> information_schema.tables where table_schema='public';")
> do
>   /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i
> rename column $OLDCOLUMN to $NEWCOLUMN;"
> done
>
> Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your
> requirement.
>
> One more way from command line

-bash-4.1$ OLDCOLUMN=xyz
-bash-4.1$ NEWCOLUMN=abc
-bash-4.1$ psql -c "select 'alter table '||table_schema||'.'||table_name||'
rename column
$OLDCOLUMN
 to
$NEWCOLUMN
;' from information_schema.tables where table_schema='public';" | psql

--Raghav

Re: [GENERAL] How to rename the same column name in dozens of tables in Postgres?

[Raghavendra]

On Tue, Dec 17, 2013 at 1:29 PM, Stefan Schwarzer  wrote:

> Hi there,
>
> I have dozens of tables where I need to rename one column in each of
> these. Is there any generic way to do that?
>
I am not really familiar with the scripting possibilities of Postgres.
>
> Thanks a lot for your help!
>
> Stefan
>
>
You can do with script. I made one on fly for this, other's might have more
polished version.

-bash-4.1$ more rename.sh
#!/bin/bash
OLDCOLUMN=aa
NEWCOLUMN=a
for i in $(psql -t -c "select table_schema||'.'||table_name from
information_schema.tables where table_schema='public';")
do
  /opt/PostgreSQL/9.3/bin/psql -p 5432 -U postgres -c "alter table $i
rename column $OLDCOLUMN to $NEWCOLUMN;"
done

Replace the port,user,OLDCOLUMN, NEWCOLUMN and SCHEMA according to your
requirement.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Rahila Syed]

Hello,

Yes, gzip compression can be used for compressing WAL traffic during
streaming replication  Following tools can be used in this regard.
SSL compression-SSL support is built in PostgreSQL. You need to ensure
you have OpenSSL library support in your PostgreSQL installation.
Also, you can compress WAL traffic by setting up SSH tunneling between
master and standby and turn on compression while setting up SSH
tunnel. Following link can be followed for the same.
http://www.postgresql.org/docs/9.3/static/ssh-tunnels.html
Ofcourse, these are the solutions outside PostgreSQL.


On Tue, Dec 10, 2013 at 4:43 AM, Dmitry Koterov  wrote:
>
> Hello.
>
> Is there a way to compress the traffic between master and slave during the 
> replication?.. The streaming gzip would be quite efficient for that.
>
> (WAL archiving is not too good for this purpose because of high lag. I just 
> need to minimize the cross-datacenter traffic keeping the replication lag 
> low.)


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

[GENERAL] How to rename the same column name in dozens of tables in Postgres?

[Stefan Schwarzer]

Hi there,

I have dozens of tables where I need to rename one column in each of these. Is 
there any generic way to do that?

I am not really familiar with the scripting possibilities of Postgres.

Thanks a lot for your help!

Stefan

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