Re: [GENERAL] question on parallelism

[Chris Travers]

On Wed, Jan 1, 2014 at 7:35 PM, Andrew McIntyre  wrote:

>  does postgres have this capability? specifically local intrapartition?
>
> http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.partition.doc/doc/c0004557.html
>
> Nope.  Here's a quick breakdown of what is done in parallel and what is
done sequentially in PostgreSQL.

Separate queries run in parallel, and can piggy back on eachother's
sequential scans.  In essence all of the work that is done in parallelism
affects parallel queries.

There is no intra-query parallelism however.  This means that partitioning
doesn't generally help here (although sequential scans on the same
partition by different queries can run in parallel with good results, a
single query cannot run several scans on different partitions in parallel).

If you need this sort of feature you are going to either need to go to a
related product (like Postgres-XC) or you are going to need to have some
other layer that can do the parallelism.

Best Wishes,
Chris Travers


> sorry for all the newbie pg questions...
> --
>  --
>  Andrew McIntyre
> amcint...@m-m.com
> http://www.mindspring.com/~amcintyr/resume.htm
>  --
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

[GENERAL] question on parallelism

[Andrew McIntyre]

does postgres have this capability? specifically local intrapartition?
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.partition.doc/doc/c0004557.html

sorry for all the newbie pg questions...
--
personal Signature

Andrew McIntyre
amcint...@m-m.com 
http://www.mindspring.com/~amcintyr/resume.htm 

[GENERAL] pg_largeobject related issue with 9.2

[sramay]

Hi,

I am having a application which was running on Jboss 5 with Hibernate and
PostgreSQL 9.2.  Due to media corruption.  Data without  largeobject was
restored  and largeobject I restored from some other source.

Now the application is giving error  eventhough largeobject is present it is
giving error.  Can any help me?
--- part of log ---

Caused by: org.hibernate.exception.SQLGrammarException: could not execute
query
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at
org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at
org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at
org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at nic.scbpds.db.DataBaseUtil.getUnionList(Unknown Source)
at
nic.scbpds.allocation.business.CentralAllocationImpl.getUnionList(Unknown
Source)
at
nic.scbpds.allocation.form.common.controller.AllocationController.getUnionList(Unknown
Source)
at nic.scbpds.allocation.form.CentralDashboardWebPage.(Unknown
Source)
at nic.scbpds.allocation.form.CentralDashboardWebPage.(Unknown
Source)
... 39 more
*Caused by: org.postgresql.util.PSQLException: ERROR: large object 141066
does not exist*
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
at
org.postgresql.core.v3.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:650)
at
org.postgresql.core.v3.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:480)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:72)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:112)
at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:124)
at
org.postgresql.largeobject.LargeObject.(LargeObject.java:91)
at
org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:200)
at
org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:170)
at
org.postgresql.jdbc2.AbstractJdbc2BlobClob.(AbstractJdbc2BlobClob.java:45)
at
org.postgresql.jdbc2.AbstractJdbc2Blob.(AbstractJdbc2Blob.java:19)
at
org.postgresql.jdbc3.AbstractJdbc3Blob.(AbstractJdbc3Blob.java:17)
at
org.postgresql.jdbc4.AbstractJdbc4Blob.(AbstractJdbc4Blob.java:18)
at org.postgresql.jdbc4.Jdbc4Blob.(Jdbc4Blob.java:18)
at
org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(Jdbc4ResultSet.java:49)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:344)
at
org.jboss.resource.adapter.jdbc.WrappedResultSet.getBlob(WrappedResultSet.java:386)
at org.hibernate.type.BlobType.get(BlobType.java:57)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:111)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at
org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2031)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)
at
org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1299)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:568)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 50 more

Thanks in advance

Rama




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-largeobject-related-issue-with-9-2-tp5784969.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] How to list and describe tables in psql???

[Ian Lawrence Barwick]

2014/1/2 peterlen :
> Thanks for the responses.  I found that I was using an older version of psql
> (one that got installed when I installed Cygwin on my windows box.   I
> changed it so now the \d+
>  gives  me the description but \d and \dt  still gives me "No relations
> found" so I must be using those incorrectly.

Possibly there's an issue with your search path?

Check if "\dn" lists schemas not listed by "SHOW search_path", if so
you can either explicitly include the schema name (\dt some_schema.*) or
adjust your search path.

See also:

http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH

Regards

Ian Barwick


-- 
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] How to list and describe tables in psql???

[peterlen]

Thanks for the responses.  I found that I was using an older version of psql
(one that got installed when I installed Cygwin on my windows box.   I
changed it so now the \d+ 
 gives  me the description but \d and \dt  still gives me "No relations
found" so I must be using those incorrectly.  Thanks for the info on the old
version.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-list-and-describe-tables-in-psql-tp5785006p5785013.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] How to list and describe tables in psql???

[Ian Lawrence Barwick]

014/1/2 peterlen :
> I am using PostgreSQL 9.1 and have boon looking for psql commands to list all
> tables in the current database as well as how to "describe" a given table.
> I have come across some info on the web (older mostly) that lists commands
> like \d, \dt, or \d+  but I get results like "no relations found"
> or "column reltriggers does not exist".  Not sure what is going on.
>
> Can someone enlighten me on the right commands to use?

Those are the correct commands, but it sounds like you're using an older
psql version against a newer server. Which psql version (psql -V) are you using?

Regards

Ian Barwick


-- 
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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Tom Lane]

Adrian Klaver  writes:
> On 01/01/2014 12:45 PM, Reiser, John J. wrote:
>> Thanks for the info. After searching the output for the connection string,
>> I found that it's failing on a database that can be archived. I think I'll
>> get what I need from the database, drop it, then perform the upgrade.

> Well I think Tom was looking for that information to not only solve your 
> issue but also to determine whether this is a bug that needs to be 
> fixed. So providing that information would help the greater cause if it 
> is something you can do.

Yes, exactly --- I think this probably indicates a previously-unknown
bug in the pg_upgrade process.

If you still have the pg_upgrade_dump_NNN.custom file corresponding to the
failing database, and don't mind showing someone else your schema
definitions (not table data, it doesn't contain any of that), would you be
willing to send me that file off-list?

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] How to list and describe tables in psql???

[Adrian Klaver]

On 01/01/2014 05:53 PM, peterlen wrote:

I am using PostgreSQL 9.1 and have boon looking for psql commands to list all
tables in the current database as well as how to "describe" a given table.
I have come across some info on the web (older mostly) that lists commands
like \d, \dt, or \d+  but I get results like "no relations found"
or "column reltriggers does not exist".  Not sure what is going on.

Can someone enlighten me on the right commands to use?


Fresh from the docs:

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

Search for:

Meta-Commands

There will be a copious amount of information available.



Thanks - Peter



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-list-and-describe-tables-in-psql-tp5785006.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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

[GENERAL] How to list and describe tables in psql???

[peterlen]

I am using PostgreSQL 9.1 and have boon looking for psql commands to list all
tables in the current database as well as how to "describe" a given table. 
I have come across some info on the web (older mostly) that lists commands
like \d, \dt, or \d+  but I get results like "no relations found"
or "column reltriggers does not exist".  Not sure what is going on.

Can someone enlighten me on the right commands to use?

Thanks - Peter



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-list-and-describe-tables-in-psql-tp5785006.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 on IPC vs TCPIP

[Martijn van Oosterhout]

On Wed, Jan 01, 2014 at 03:55:50PM -0500, Andrew McIntyre wrote:
> Does postgres have an equivalent C level (or ODBC) parameter so you
> can use IPC for local to db server only code?
> http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008807.html
> 
> There is a measurable reduction in elapsed time for my code when I
> specify IPC. My code uses:
> node.js
> https://npmjs.org/package/odbc
> db2

In general, in postgres you leave the the hostname blank to specify
local IPC. 

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Adrian Klaver]

On 01/01/2014 12:45 PM, Reiser, John J. wrote:


On 1/1/14, 3:37 PM, "Tom Lane"  wrote:





Could you look at the text surrounding these places to determine which
types this OID is being selected for?  Each of these calls should be just
preceding a CREATE TYPE command (with maybe a set_next_array_pg_type_oid
call between) that is supposed to use the specified OID for its type.
Also identify which databases the commands are being issued in, by looking
back for the most recent \connect command.

Also, is there any CREATE TYPE for st_envelope preceding the failing
CREATE FUNCTION command (in the same database)?

regards, tom lane


Tom,

Thanks for the info. After searching the output for the connection string,
I found that it's failing on a database that can be archived. I think I'll
get what I need from the database, drop it, then perform the upgrade.


Well I think Tom was looking for that information to not only solve your 
issue but also to determine whether this is a bug that needs to be 
fixed. So providing that information would help the greater cause if it 
is something you can do.




Thank you again for all of your help. It's greatly appreciated!

John







--
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

[GENERAL] question on IPC vs TCPIP

[Andrew McIntyre]

Does postgres have an equivalent C level (or ODBC) parameter so you can 
use IPC for local to db server only code?

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008807.html

There is a measurable reduction in elapsed time for my code when I 
specify IPC. My code uses:

node.js
https://npmjs.org/package/odbc
db2

Thanks!!
--
personal Signature

Andrew McIntyre
amcint...@m-m.com 
http://www.mindspring.com/~amcintyr/resume.htm 

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Reiser, John J.]

On 1/1/14, 3:37 PM, "Tom Lane"  wrote:

>"Reiser, John J."  writes:
>> On 1/1/14, 12:38 PM, "Tom Lane"  wrote:
>>> What this smells like is a bug in the pg_dump --binary_upgrade logic
>>>that
>>> tries to preserve type OIDs from the old installation to the new one.
>>> Is there a preceding CREATE TYPE command for st_envelope in the dump
>>> script?  Look for calls to binary_upgrade.set_next_pg_type_oid() and
>>> binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
>>> there conflicting entries?  Also, exactly what is type 1407909 in the
>>> old installation (try "select * from pg_type where oid = 1407909")?
>
>> Once I got 8.4 back up, I searched for that OID in pg_type. select *
>>from
>> pg_type where oid = 1407909; returns 0 rows.
>
>Hm, which database(s) did you check in?  It certainly appears from the
>dump text you quote that type "spheroid" has OID 1407909 in at least one
>database.
>
>> I did find this, searching through pg_upgrade_restore.log. There are 8
>> instances of the following text in the file:
>
>If I'm reading you right, then these must be instances of the same type
>with the same OID declared in different databases.  Could you look through
>the dump for \connect commands to verify that?
>
>> SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
>> occurs 22 times on lines (1150, 1176, 44192, 44218, 64149, 64175, 71815,
>> 71841, 79844, 79870, 88982, 89008, 97153, 97179, 106523, 106549, 289254,
>> 289280, 297653, 297679, 310824, 310850) and all the close pairs are 26
>> lines apart, like the excerpt copied above.
>
>Could you look at the text surrounding these places to determine which
>types this OID is being selected for?  Each of these calls should be just
>preceding a CREATE TYPE command (with maybe a set_next_array_pg_type_oid
>call between) that is supposed to use the specified OID for its type.
>Also identify which databases the commands are being issued in, by looking
>back for the most recent \connect command.
>
>Also, is there any CREATE TYPE for st_envelope preceding the failing
>CREATE FUNCTION command (in the same database)?
>
>   regards, tom lane

Tom, 

Thanks for the info. After searching the output for the connection string,
I found that it's failing on a database that can be archived. I think I'll
get what I need from the database, drop it, then perform the upgrade.

Thank you again for all of your help. It's greatly appreciated!

John




-- 
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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Tom Lane]

"Reiser, John J."  writes:
> On 1/1/14, 12:38 PM, "Tom Lane"  wrote:
>> What this smells like is a bug in the pg_dump --binary_upgrade logic that
>> tries to preserve type OIDs from the old installation to the new one.
>> Is there a preceding CREATE TYPE command for st_envelope in the dump
>> script?  Look for calls to binary_upgrade.set_next_pg_type_oid() and
>> binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
>> there conflicting entries?  Also, exactly what is type 1407909 in the
>> old installation (try "select * from pg_type where oid = 1407909")?

> Once I got 8.4 back up, I searched for that OID in pg_type. select * from
> pg_type where oid = 1407909; returns 0 rows.

Hm, which database(s) did you check in?  It certainly appears from the
dump text you quote that type "spheroid" has OID 1407909 in at least one
database.

> I did find this, searching through pg_upgrade_restore.log. There are 8
> instances of the following text in the file:

If I'm reading you right, then these must be instances of the same type
with the same OID declared in different databases.  Could you look through
the dump for \connect commands to verify that?

> SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
> occurs 22 times on lines (1150, 1176, 44192, 44218, 64149, 64175, 71815,
> 71841, 79844, 79870, 88982, 89008, 97153, 97179, 106523, 106549, 289254,
> 289280, 297653, 297679, 310824, 310850) and all the close pairs are 26
> lines apart, like the excerpt copied above.

Could you look at the text surrounding these places to determine which
types this OID is being selected for?  Each of these calls should be just
preceding a CREATE TYPE command (with maybe a set_next_array_pg_type_oid
call between) that is supposed to use the specified OID for its type.
Also identify which databases the commands are being issued in, by looking
back for the most recent \connect command.

Also, is there any CREATE TYPE for st_envelope preceding the failing
CREATE FUNCTION command (in the same database)?

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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Reiser, John J.]

On 1/1/14, 12:38 PM, "Tom Lane"  wrote:

>"Reiser, John J."  writes:
>> I'm working on an upgrade to our database cluster, attempting to move
>>from 8.4 to 9.2. I'm encountering the following error when I attempt the
>>upgrade (in pg_upgrade_restore.log):
>
>> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>> LANGUAGE "c" IMMUTABLE STRICT
>> AS 'st_geometry', 'ST_ENVELOPE_In';
>> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value
>>violates unique constraint "pg_type_oid_index"
>> DETAIL:  Key (oid)=(1407909) already exists.
>
>What this smells like is a bug in the pg_dump --binary_upgrade logic that
>tries to preserve type OIDs from the old installation to the new one.
>Is there a preceding CREATE TYPE command for st_envelope in the dump
>script?  Look for calls to binary_upgrade.set_next_pg_type_oid() and
>binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
>there conflicting entries?  Also, exactly what is type 1407909 in the
>old installation (try "select * from pg_type where oid = 1407909")?

Once I got 8.4 back up, I searched for that OID in pg_type. select * from
pg_type where oid = 1407909; returns 0 rows.

I did find this, searching through pg_upgrade_restore.log. There are 8
instances of the following text in the file:

CREATE TYPE "pgis_abs" (
INTERNALLENGTH = 8,
INPUT = pgis_abs_in,
OUTPUT = pgis_abs_out,
ALIGNMENT = double,
STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."pgis_abs" OWNER TO "reiser";
ALTER TYPE
SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
 set_next_pg_type_oid
--
 
(1 row)

SELECT 
binary_upgrade.set_next_array_pg_type_oid('1407914'::pg_catalog.oid);
 set_next_array_pg_type_oid

 
(1 row)

CREATE TYPE "spheroid";
CREATE TYPE
CREATE FUNCTION "spheroid_in"(cstring) RETURNS spheroid
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/postgis-1.5', 'ellipsoid_in';
CREATE FUNCTION
ALTER FUNCTION "public"."spheroid_in"(cstring) OWNER TO "reiser";
ALTER FUNCTION
CREATE FUNCTION "spheroid_out"(spheroid) RETURNS cstring
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/postgis-1.5', 'ellipsoid_out';
CREATE FUNCTION
ALTER FUNCTION "public"."spheroid_out"(spheroid) OWNER TO "reiser";
ALTER FUNCTION
SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
 set_next_pg_type_oid
--
 
(1 row)

SELECT 
binary_upgrade.set_next_array_pg_type_oid('1407914'::pg_catalog.oid);
 set_next_array_pg_type_oid

 
(1 row)

CREATE TYPE "spheroid" (
INTERNALLENGTH = 65,
INPUT = spheroid_in,
OUTPUT = spheroid_out,
ALIGNMENT = double,
STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."spheroid" OWNER TO "reiser";
ALTER TYPE
CREATE FUNCTION "_st_asgeojson"(integer, geometry, integer, integer)
RETURNS text
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/postgis-1.5', 'LWGEOM_asGeoJson';
CREATE FUNCTION
ALTER FUNCTION "public"."_st_asgeojson"(integer, geometry, integer,
integer) OWNER TO "reiser";
ALTER FUNCTION





The end of the file differs in that creation of the st_envelope_in
function is attempted instead of _st_asgeojson.

CREATE FUNCTION "st_envelope_in"
is only in the file 5 times (one being just before the error) and here it
is in context:

SET search_path = "sde", pg_catalog;
SET
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_geometry', 'ST_ENVELOPE_In';
CREATE FUNCTION
ALTER FUNCTION "sde"."st_envelope_in"(cstring) OWNER TO "sde";
ALTER FUNCTION

The line
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
occurs on lines 9076, 106654, 139095, 164850 and 310874 (the error)

and 
SELECT binary_upgrade.set_next_pg_type_oid('1407909'::pg_catalog.oid);
occurs 22 times on lines (1150, 1176, 44192, 44218, 64149, 64175, 71815,
71841, 79844, 79870, 88982, 89008, 97153, 97179, 106523, 106549, 289254,
289280, 297653, 297679, 310824, 310850) and all the close pairs are 26
lines apart, like the excerpt copied above.


Any insight you can provide would be greatly appreciated.

Thanks,
John


>
>> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the
>>Yum repository. PostgreSQL is primarily used for GIS data and has ESRI
>>st_geometry and PostGIS installed in several of the databases. (ESRI's
>>support is only up to 9.2, which is why I'm not attempting a move to
>>9.3.) The interesting thing with this error is that when I wipe out the
>>9.2 data directory, re-initdb, and run the upgrade again, I now get a
>>different error:
>
>> CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
>> psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory
>>"/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace
>
>I think that's pilot error: you forgot to clean out tablespace directories
>along with the main data directory.
>
>   regards, tom lane




-- 
Sent

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Tom Lane]

"Reiser, John J."  writes:
> I'm working on an upgrade to our database cluster, attempting to move from 
> 8.4 to 9.2. I'm encountering the following error when I attempt the upgrade 
> (in pg_upgrade_restore.log):

> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
> LANGUAGE "c" IMMUTABLE STRICT
> AS 'st_geometry', 'ST_ENVELOPE_In';
> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates 
> unique constraint "pg_type_oid_index"
> DETAIL:  Key (oid)=(1407909) already exists.

What this smells like is a bug in the pg_dump --binary_upgrade logic that
tries to preserve type OIDs from the old installation to the new one.
Is there a preceding CREATE TYPE command for st_envelope in the dump
script?  Look for calls to binary_upgrade.set_next_pg_type_oid() and
binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
there conflicting entries?  Also, exactly what is type 1407909 in the
old installation (try "select * from pg_type where oid = 1407909")?

> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the Yum 
> repository. PostgreSQL is primarily used for GIS data and has ESRI 
> st_geometry and PostGIS installed in several of the databases. (ESRI's 
> support is only up to 9.2, which is why I'm not attempting a move to 9.3.) 
> The interesting thing with this error is that when I wipe out the 9.2 data 
> directory, re-initdb, and run the upgrade again, I now get a different error:

> CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
> psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory 
> "/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace

I think that's pilot error: you forgot to clean out tablespace directories
along with the main data directory.

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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Reiser, John J.]

Adrian,


On 1/1/14, 12:26 PM, "Adrian Klaver"  wrote:

>On 01/01/2014 09:08 AM, Reiser, John J. wrote:
>> The --link argument doesn't work, either:
>>
>
>>
>> Consult the last few lines of "pg_upgrade_restore.log" for
>> the probable cause of the failure.
>> Failure, exiting
>> bash-4.1$ tail -n 20 pg_upgrade_restore.log
>> (1 row)
>>
>> CREATE TYPE "spheroid" (
>>  INTERNALLENGTH = 65,
>>  INPUT = spheroid_in,
>>  OUTPUT = spheroid_out,
>>  ALIGNMENT = double,
>>  STORAGE = plain
>> );
>> CREATE TYPE
>> ALTER TYPE "public"."spheroid" OWNER TO "reiser";
>> ALTER TYPE
>> SET search_path = "sde", pg_catalog;
>> SET
>> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
>>  LANGUAGE "c" IMMUTABLE STRICT
>>  AS 'st_geometry', 'ST_ENVELOPE_In';
>> psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates
>> unique constraint "pg_type_oid_index"
>> DETAIL:  Key (oid)=(1407909) already exists.
>>
>> Again, any help that you could provide would be greatly appreciated.
>
>So have you already installed the GIS stuff into the 9.2 cluster before
>the upgrade?

I have installed PostGIS 1.5.8 and the st_geometry.so file that ESRI
requires into the 9.2 directory (/usr/pgsql-9.2/lib). pg_upgrade wouldn't
proceed as far as it did without doing that.

I received your other email and will try the upgrade again and compare the
OIDs in new and old. I'll email the list again once I've done that.

Thanks again for your help.


>> John
>>
>
>
>-- 
>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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Adrian Klaver]

On 01/01/2014 09:08 AM, Reiser, John J. wrote:

The --link argument doesn't work, either:





Consult the last few lines of "pg_upgrade_restore.log" for
the probable cause of the failure.
Failure, exiting
bash-4.1$ tail -n 20 pg_upgrade_restore.log
(1 row)

CREATE TYPE "spheroid" (
 INTERNALLENGTH = 65,
 INPUT = spheroid_in,
 OUTPUT = spheroid_out,
 ALIGNMENT = double,
 STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."spheroid" OWNER TO "reiser";
ALTER TYPE
SET search_path = "sde", pg_catalog;
SET
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
 LANGUAGE "c" IMMUTABLE STRICT
 AS 'st_geometry', 'ST_ENVELOPE_In';
psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates
unique constraint "pg_type_oid_index"
DETAIL:  Key (oid)=(1407909) already exists.

Again, any help that you could provide would be greatly appreciated.


So have you already installed the GIS stuff into the 9.2 cluster before 
the upgrade?



John




--
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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Adrian Klaver]

On 01/01/2014 08:53 AM, Reiser, John J. wrote:

Hello,

I'm working on an upgrade to our database cluster, attempting to move
from 8.4 to 9.2. I'm encountering the following error when I attempt the
upgrade (in pg_upgrade_restore.log):

CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
 LANGUAGE "c" IMMUTABLE STRICT
 AS 'st_geometry', 'ST_ENVELOPE_In';
psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value
violates unique constraint "pg_type_oid_index"
DETAIL:  Key (oid)=() already exists.


Well this means an OID is being used twice in the system catalog 
pg_type. You could look up that oid(1407909) in the 8.4 pg_type and see 
what it is. Also look it up in the fresh pg_type when you init the 9.2 
cluster.




I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the
Yum repository. PostgreSQL is primarily used for GIS data and has ESRI
st_geometry and PostGIS installed in several of the databases. (ESRI's
support is only up to 9.2, which is why I'm not attempting a move to
9.3.) The interesting thing with this error is that when I wipe out the
9.2 data directory, re-initdb, and run the upgrade again, I now get a
different error:

CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory
"/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace


When you ran the upgrade above it probably got as far as creating the 
9.2 tablespaces in /disk2/pgsql/data/sde. You now have two versions of 
the tablespaces, one labeled PG_8.4_* and the other PG_9.2_201204301. 
Along with wiping out the 9.2 data directory you need to wipe out the 
9.2 tablespace directory.





(I have several of our ESRI SDE databases in their own tablespace.)

Before starting this process, I made a complete file-based backup of the
8.4 data directory. When I restore the backup to /var/lib/pgsql and run
pg_upgrade again, I receive the first error again, with the same exact
OID value. I will admit I don't know much about Postgres internals and
I'm not sure how to proceed with this duplicate OID issue.

I'm going to try running pg_upgrade with the link option now, but I
don't know if that will help.

Any assistance provided would be greatly appreciated.

Thanks,
John

John Reiser

/Geospatial Research Lab /

*Rowan University *

201 Mullica Hill Road

Glassboro, NJ 08028

phone: 856-256-4817

cell: 856-347-0047

twitter: @rowangeolab 




--
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] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Reiser, John J.]

The --link argument doesn't work, either:

bash-4.1$ export LD_LIBRARY_PATH=/usr/pgsql-9.2/lib
bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/data 
--new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/bin 
--new-bindir=/usr/pgsql-9.2/bin --check
Performing Consistency Checks
-
Checking current, bin, and data directories ok
Checking cluster versions   ok
Checking database user is a superuser   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for large objects  ok
Checking for presence of required libraries ok
Checking database user is a superuser   ok
Checking for prepared transactions  ok

*Clusters are compatible*

bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/data 
--new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/bin 
--new-bindir=/usr/pgsql-9.2/bin --link
Performing Consistency Checks
-
Checking current, bin, and data directories ok
Checking cluster versions   ok
Checking database user is a superuser   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for large objects  ok
Creating catalog dump   ok
Checking for presence of required libraries ok
Checking database user is a superuser   ok
Checking for prepared transactions  ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server   ok
Setting next transaction ID for new cluster ok
Resetting WAL archives  ok
Setting frozenxid counters in new cluster   ok
Creating databases in the new cluster   ok
Adding support functions to new cluster ok
Restoring database schema to new cluster*failure*

Consult the last few lines of "pg_upgrade_restore.log" for
the probable cause of the failure.
Failure, exiting
bash-4.1$ tail -n 20 pg_upgrade_restore.log

(1 row)

CREATE TYPE "spheroid" (
INTERNALLENGTH = 65,
INPUT = spheroid_in,
OUTPUT = spheroid_out,
ALIGNMENT = double,
STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."spheroid" OWNER TO "reiser";
ALTER TYPE
SET search_path = "sde", pg_catalog;
SET
CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_geometry', 'ST_ENVELOPE_In';
psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates unique 
constraint "pg_type_oid_index"
DETAIL:  Key (oid)=(1407909) already exists.

Again, any help that you could provide would be greatly appreciated.
John

From: , John Reiser mailto:rei...@rowan.edu>>
Date: Wednesday, January 1, 2014 at 11:53 AM
To: "pgsql-general@postgresql.org" 
mailto:pgsql-general@postgresql.org>>
Subject: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 
to 9.2

Hello,

I'm working on an upgrade to our database cluster, attempting to move from 8.4 
to 9.2. I'm encountering the following error when I attempt the upgrade (in 
pg_upgrade_restore.log):

CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_geometry', 'ST_ENVELOPE_In';
psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates unique 
constraint "pg_type_oid_index"
DETAIL:  Key (oid)=(1407909) already exists.

I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the Yum 
repository. PostgreSQL is primarily used for GIS data and has ESRI st_geometry 
and PostGIS installed in several of the databases. (ESRI's support is only up 
to 9.2, which is why I'm not attempting a move to 9.3.) The interesting thing 
with this error is that when I wipe out the 9.2 data directory, re-initdb, and 
run the upgrade again, I now get a different error:

CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory 
"/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace

(I have several of our ESRI SDE databases in their own tablespace.)

Before starting this proc

[GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

[Reiser, John J.]

Hello,

I'm working on an upgrade to our database cluster, attempting to move from 8.4 
to 9.2. I'm encountering the following error when I attempt the upgrade (in 
pg_upgrade_restore.log):

CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_geometry', 'ST_ENVELOPE_In';
psql:pg_upgrade_dump_db.sql:371910: ERROR:  duplicate key value violates unique 
constraint "pg_type_oid_index"
DETAIL:  Key (oid)=(1407909) already exists.

I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the Yum 
repository. PostgreSQL is primarily used for GIS data and has ESRI st_geometry 
and PostGIS installed in several of the databases. (ESRI's support is only up 
to 9.2, which is why I'm not attempting a move to 9.3.) The interesting thing 
with this error is that when I wipe out the 9.2 data directory, re-initdb, and 
run the upgrade again, I now get a different error:

CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
psql:pg_upgrade_dump_globals.sql:294: ERROR:  directory 
"/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace

(I have several of our ESRI SDE databases in their own tablespace.)

Before starting this process, I made a complete file-based backup of the 8.4 
data directory. When I restore the backup to /var/lib/pgsql and run pg_upgrade 
again, I receive the first error again, with the same exact OID value. I will 
admit I don't know much about Postgres internals and I'm not sure how to 
proceed with this duplicate OID issue.

I'm going to try running pg_upgrade with the link option now, but I don't know 
if that will help.

Any assistance provided would be greatly appreciated.

Thanks,
John

John Reiser
Geospatial Research Lab
Rowan University
201 Mullica Hill Road
Glassboro, NJ 08028
phone: 856-256-4817
cell: 856-347-0047
twitter: @rowangeolab

Re: [GENERAL] How to delete completely duplicate rows

[bricklen]

On Wed, Jan 1, 2014 at 4:14 AM, Janek Sendrowski  wrote:

> I want to delete duplicates in my table. I've dropped the unique
> constraint to insert my data.
> My id value is a hash calculated witch the values of the two other columns.
> So I want to delete all columns, which are indentical, but keeping one.
>
> DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid >
> t2.ctid
>
> But the oids aren't unique enough.
> What else could I do?


http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Delete_duplicate_rows_with_window_analytic_functions
http://wiki.postgresql.org/wiki/Deleting_duplicates

Re: [GENERAL] Refresh Materialized View

[Jayadevan M]

To answer my own question, I saw this thread -
http://postgresql.1045698.n5.nabble.com/Efficiency-of-materialized-views-refresh-in-9-3-td5762618.html
I think that does answer my questions.  Nothing has changed?
Regards,
Jayadevan


On Wed, Jan 1, 2014 at 6:42 PM, Jayadevan M wrote:

> Hello all,
> A few questions about materialized views.
> When I refresh a materialized view, is it a DELETE/INSERT behind the
> scenes?
> Do we need to vacuum to reclaim space?
> If a query is executed against the view when the refresh is happening,
> will the query see the data before the refresh started?
> Does the refresh result in exclusive locking?
> Regards,
> Jayadevan
>

[GENERAL] Refresh Materialized View

[Jayadevan M]

Hello all,
A few questions about materialized views.
When I refresh a materialized view, is it a DELETE/INSERT behind the
scenes?
Do we need to vacuum to reclaim space?
If a query is executed against the view when the refresh is happening, will
the query see the data before the refresh started?
Does the refresh result in exclusive locking?
Regards,
Jayadevan

Re: [GENERAL] How to delete completely duplicate rows

[Erik Darling]

With C as (
Select row_number() over partition by (list, all, columns, here order by
oid) as rn
)
Delete
>From C
Where rn > 1;
 On Jan 1, 2014 7:15 AM, "Janek Sendrowski"  wrote:

> Hi,
>
> I want to delete duplicates in my table. I've dropped the unique
> constraint to insert my data.
> My id value is a hash calculated witch the values of the two other columns.
> So I want to delete all columns, which are indentical, but keeping one.
>
> DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid >
> t2.ctid
>
> But the oids aren't unique enough.
> What else could I do?
>
> Janek
>
>
> --
> 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 delete completely duplicate rows

[Janek Sendrowski]

Hi,
 
I want to delete duplicates in my table. I've dropped the unique constraint to 
insert my data.
My id value is a hash calculated witch the values of the two other columns.
So I want to delete all columns, which are indentical, but keeping one.
 
DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid > t2.ctid

But the oids aren't unique enough.
What else could I do?

Janek


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