Emi Lu | 29 Aug 20:33 2014
Picon
Picon

alter column to varchar without view drop/re-creation

Hello list,

May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation?

Basically, looking for a way to change column without have to drop/re-create dependent views.

varchar(***) to varchar and no date/numeric changes.

Thanks a lot!
Emi
---
PostgreSQL 8.3.18 on x86_64
Patrick Dung | 29 Aug 20:23 2014
Picon

Is there a function to save schema history internally?

Hello Postgresql users,

Is there a function to save schema history internally?
By keeping the schema history inside the DB, we can keep track of what and when is changed in the schema.

While searching google. It seems it is a limitation with the audit trigger:

Thanks and regards,
Patrick
Emmanuel Medernach | 29 Aug 15:52 2014
Picon
Picon

Performance issue with postgres_fdw

Hello,

I use  Postgres version 9.3.5  and spot a  performance issue
with postgres_fdw.

I  have a  table  object_003_xyz with  275000  lines and  is
exported to the master node as master_object_003_xyz.

( The  following query  is  only a  part  of an  automatically
generated complex query. )

On the master:

SELECT * FROM master_object_003_xyz AS o1, master_object_003_xyz AS o2 WHERE 
o1.objectid <> o2.objectid  AND cos(radians(o1.ra_PS))  * 
cos(radians(o1.decl_PS)) BETWEEN cos(radians(o2.ra_PS))  * 
cos(radians(o2.decl_PS))  - sin(radians(1.5e-5  / 2))  * 2 AND 
cos(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  + sin(radians(1.5e-5  / 2)) 
  * 2  AND sin(radians(o1.ra_PS))  * cos(radians(o1.decl_PS)) BETWEEN 
sin(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  - sin(radians(1.5e-5  / 2)) 
  * 2 AND sin(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  + 
sin(radians(1.5e-5  / 2))  * 2 AND sin(radians(o1.decl_PS)) BETWEEN 
sin(radians(o2.decl_PS))  - sin(radians(1.5e-5  / 2))  * 2 AND 
sin(radians(o2.decl_PS))  + sin(radians(1.5e-5  / 2))  * 2 AND 
degrees(asin(sqrt(power(sin(radians((o2.decl_PS  - o1.decl_PS)  / 2)), 2)  + 
power(sin(radians((o2.ra_PS  - o1.ra_PS)  / 2)), 2)  * cos(radians(o1.decl_PS)) 
* cos(radians(o2.decl_PS))))  * 2) <= 1.5e-5
(4 rows)
Time: 513711.684 ms

Here is the plan used:

  Nested Loop  (cost=200.70..44187032.64 rows=34518880 width=2168)
    ->  Foreign Scan on master_object_003_xyz o2  (cost=100.00..24294.47 
rows=275449 width=1084)
    ->  Foreign Scan on master_object_003_xyz o1  (cost=100.70..160.32 rows=1 
width=1084)

On the pool:

SELECT * FROM object_003_xyz AS o1, object_003_xyz AS o2 WHERE o1.objectid <> 
o2.objectid  AND cos(radians(o1.ra_PS))  * cos(radians(o1.decl_PS)) BETWEEN 
cos(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  - sin(radians(1.5e-5  / 2)) 
  * 2 AND cos(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  + 
sin(radians(1.5e-5  / 2))  * 2  AND sin(radians(o1.ra_PS))  * 
cos(radians(o1.decl_PS)) BETWEEN sin(radians(o2.ra_PS))  * 
cos(radians(o2.decl_PS))  - sin(radians(1.5e-5  / 2))  * 2 AND 
sin(radians(o2.ra_PS))  * cos(radians(o2.decl_PS))  + sin(radians(1.5e-5  / 2)) 
  * 2 AND sin(radians(o1.decl_PS)) BETWEEN sin(radians(o2.decl_PS))  - 
sin(radians(1.5e-5  / 2))  * 2 AND sin(radians(o2.decl_PS))  + 
sin(radians(1.5e-5  / 2))  * 2 AND
degrees(asin(sqrt(power(sin(radians((o2.decl_PS  - o1.decl_PS)  / 2)), 2)  + 
power(sin(radians((o2.ra_PS  - o1.ra_PS)  / 2)), 2)  * cos(radians(o1.decl_PS)) 
* cos(radians(o2.decl_PS))))  * 2) <= 1.5e-5
(4 rows)
Time: 2738.217 ms

It is much faster because it uses available index :

  Nested Loop  (cost=0.56..360279717.93 rows=34692216 width=2168)
    ->  Seq Scan on object_003_xyz o2  (cost=0.00..18685.49 rows=275449 width=1084)
    ->  Index Scan using object_003_xyz_idx_xyz on object_003_xyz o1 
(cost=0.56..1306.64 rows=126 width=1084)
          Index Cond: (((cos(radians(ra_ps)) * cos(radians(decl_ps))) >= 
((cos(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) - 
2.61799387799149e-07::double precision)) AND ((cos(radians(ra_ps)) * 
cos(radians(decl_ps))) <= ((cos(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) + 
2.61799387799149e-07::double precision)) AND ((sin(radians(ra_ps)) * 
cos(radians(decl_ps))) >= ((sin(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) - 
2.61799387799149e-07::double precision)) AND ((sin(radians(ra_ps)) * 
cos(radians(decl_ps))) <= ((sin(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) + 
2.61799387799149e-07::double precision)) AND (sin(radians(decl_ps)) >= 
(sin(radians(o2.decl_ps)) - 2.61799387799149e-07::double precision)) AND 
(sin(radians(decl_ps)) <= (sin(radians(o2.decl_ps)) + 
2.61799387799149e-07::double precision)))
          Filter: ((objectid <> o2.objectid) AND 
(degrees((asin(sqrt((power(sin(radians(((o2.decl_ps - decl_ps) / 2::double 
precision))), 2::double precision) + ((power(sin(radians(((o2.ra_ps - ra_ps) / 
2::double precision))), 2::double precision) * cos(radians(decl_ps))) * 
cos(radians(o2.decl_ps)))))) * 2::double precision)) <= 1.5e-05::double precision))

Would it be possible to avoid doing a nested loop of foreign
scans when  dealing with tables on  the same pool  ?  And to
automatically export the query directly in that case ?

What may I do for now ?

Thanks for your help and best regards,
-- 
Emmanuel Medernach

--

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

Kynn Jones | 29 Aug 16:06 2014
Picon

Transforming pg_dump output to be compatible with SQLite 3.x

Greetings!

I'm looking for tools/resources/ideas for making pg_dump's output compatible with SQLite v. 3.1.3.

Ideally, I'd love to be able to do something like this (Unix):

  % rm -f mydatabase.db
  % pg_dump --no-owner --inserts mydatabase | pg_dump2sqlite3 | sqlite3 mydatabase.db

...where pg_dump2sqlite3 stands for some program (or pipeline) that transforms the output of pg_dump as needed so that sqlite3 can digest it.

Among the tasks that the hypothetical pg_dump2sqlite3 program has to carry, IMO the hardest one to implement is to compute the foreign-key dependencies among the tables, and from this compute the sequential order in which the tables will be created and populated[1].

Am I correct?  Is there a way around this?

TIA!

kj

[1] In pg_dump's output, the sequential ordering of the CREATE TABLE statements and of the COPY blocks that respectively define and populate the tables does not take into account dependencies, because the specification of these dependencies comes after all the CREATE TABLE and COPY commands, in the form of ALTER TABLE statements.  AFAIK, however, sqlite3 does not allow adding foreign key constraints after the table has been created.  This means that both the ordering of table creation and population must respect the dependencies among the tables.
Rich Shepard | 29 Aug 15:18 2014

Single Table Report With Calculated Column

   I've read some on table partitioning and using nested select statements
with group by, but have not found the syntax to produce the needed results.

   From a table I extract row counts grouped by three columns:

select stream, sampdate, func_feed_grp, count(*) from benthos group
by stream, sampdate, func_feed_grp order by stream, sampdate,
func_feed_group;

And I want to include the proportion of each count based on the total rows
for each stream and sampdate. The totals are obtained with this statement:

select stream, sampdate, count(*) as tot_cnt from benthos group by stream,
sampdate order by stream, sampdate;

   What I do not know how to do is combine the two so the resulting table
contains the columns stream, sampdate, count, proportion. I want to learn
how to build the sub-select to get this result. Joe Celko's 'SQL for
Smarties, 4th Ed.' has a close example in the chapter on table partitioning,
but I could not apply that model to my table.

TIA,

Rich

--

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

Vinayak | 29 Aug 07:06 2014
Picon

Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions

Hello,
We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but
there is a difference in timezone.
SYSDATE returns the time on the server where the database instance is
running(returns operating system time) so the time depends on the OS
timezone setting.
while the timezone of postgreSQL
statement_timestamp()/now()/clock_timestamp() depends on the DBMS setting.
so I think timezone settings are different between DBMS and OS.
Consider the following example 
SYSDATE vs CURRENT_DATE:
SYSDATE returns the time on the server where the database instance is
running(returns operating system time) CURRENT_DATE returns the time where
the session is running In the below example sysdate and current_date return
the same time but if we set the new time zone then it shows the difference
in time. 

Example:
Oracle:
SQL> select SYSDATE,CURRENT_DATE from dual;

SYSDATE 		    CURRENT_DATE
--------------------------- ---------------------------
28-AUG-14 14:08:58	    28-AUG-14 14:08:58

SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; 
SQL>select SYSDATE,CURRENT_DATE from dual;

SYSDATE 		    CURRENT_DATE
--------------------------- ---------------------------
28-AUG-14 14:10:23	    28-AUG-14 03:40:23

PostgreSQL:
postgres=# show time zone;
   TimeZone   
--------------
 Asia/Kolkata
(1 row)

postgres=# select now();
               now                
----------------------------------
 2014-08-28 14:19:51.740664+05:30
(1 row)

postgres=# set time zone 'Europe/Rome';
SET
postgres=#
postgres=# select now();
              now              
-------------------------------
 2014-08-28 10:51:03.941594+02
(1 row)

Any idea how can we set OS timezone on PostgreSQL?

-----
Thanks and Regards,
Vinayak Pokale,
NTT DATA OSS Center Pune, India
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Timezone-difference-between-Oracle-SYSDATE-and-PostgreSQL-timestamp-functions-tp5816851.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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

Yogesh. Sharma | 28 Aug 05:22 2014
Picon

Help related to Postgresql for RHEL 6.5

Dear All,

 

I want to upgrade RHEL 5.4 to RHEL 6.5.

So, could you please let me know, which postgresql version is stable for RHEL 6.5?

 

Regards,

Yogesh

DISCLAIMER: ----------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or NEC or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of NEC or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. . -----------------------------------------------------------------------------------------------------------------------
Maeldron T. | 28 Aug 14:20 2014
Picon

Failback to old master

Hello,

I swear I have read a couple of old threads. Yet I am not sure if it safe to failback to the old master in case of async replication without base backup.

Considering:
I have the latest 9.3 server
A: master
B: slave
B is actively connected to A

I shut down A manually with -m fast (it's the default FreeBSD init script setting)
I remove the recovery.conf from B
I restart B
I create a recovery.conf on A
I start A
I see nothing wrong in the logs
I go for a lunch
I shut down B
I remove the recovery.conf on A
I restart A
I restore the recovery.conf on B
I start B
I see nothing wrong in the logs and I see that replication is working

Can I say that my data is safe in this case?

If the answer is yes, is it safe to do this if there was a power outage on A instead of manual shutdown? Considering that the log says nothing wrong. (Of course if it complains I'd do base backup from B).

Thank you,

M.

Patrick Krecker | 28 Aug 03:39 2014

WAL receive process dies

We have a periodic network connectivity issue (unrelated to Postgres) that is causing the replication to fail. 

We are running Postgres 9.3 using streaming replication. We also have WAL archives available to be replayed with restore_command. Typically when I bring up a slave it copies over WAL archives for a while before connecting via streaming replication.

When I notice the machine is behind in replication, I also notice that the WAL receiver process has died. There didn't seem to be any information in the logs about it. The solution thus far has been to restart Postgres. I have two questions:

1. It seems that Postgres does not fall back to copying WAL archives with its restore_command. I just want to confirm that this is what Postgres is supposed to do when its connection via streaming replication times out.
2. Is it possible to restart replication after the WAL receiver process has died without restarting Postgres?

Thanks!
Patrick
Rich Shepard | 28 Aug 01:13 2014

Re: UPDATE table: Syntax to Remove Terminal '\n' [RESOLVED]

On Wed, 27 Aug 2014, Jeff Ross wrote:

> You want the E in front of the entire string, not just before the \n.
> select
> 'Use Discover: ' || E'\t' || 'Yes'  || E'\n'

Jeff,

   That did the trick. Turns out that 202 of 204 rows had the newline! The
syntax that worked:

update benthos set stream = 'StarvationCrk' where stream = E'StarvationCrk\n';

Much appreciated,

Rich

--

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

Rich Shepard | 28 Aug 00:31 2014

UPDATE table: Syntax to Remove Terminal '\n'

   I have some rows in a table where a column attribute has a newline (\n)
appended to the string. How do I represent that newline character in a SQL
statement using psql?

   I've tried adding E'\n' to the end of the string but that doesn't work.

   Here's what I see when I select distinct for that column:

StarvationCrk+

   That's a blank line below the name.

TIA,

Rich

--

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


Gmane