Re: [GENERAL] techniques for bulk load of spatial data

On 2010-11-30 14.29, Mario Corchero wrote: Hi, I'm a student of Computer Science, I know diffrents techniques of bulk load, but I need to know how specifically postgreSQL make a bulk load of spatial data, could anyone Suggestions when loading large amount of data: http://www.postgresql.org/do

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

Thom, You can list available locales on your system with: locale -a Thank you. How to resolve this issue if only 5432 port is open in server ? Based on my limited knowledge, postgres does not have command to run "locale -a" How to get list of available locale names from Postgres ? Why Postg

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

On 01/12/2010 08:26, Andrus wrote: Thom, You can list available locales on your system with: locale -a Thank you. How to resolve this issue if only 5432 port is open in server ? Based on my limited knowledge, postgres does not have command to run "locale -a" How to get list of available loca

[GENERAL] A question about timezone

Hello, Sorry if I have posted this to the wrong mailing list. Its my first post. I have a problem that I hope someone can help me with. I have inherited a postgresql DB which has a table containing a day of the week, a time and a time zone. I need to translate all these times into UCT but I need

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

On ons, 2010-12-01 at 10:26 +0200, Andrus wrote: > How to get list of available locale names from Postgres ? > Why Postgres does not have command which returns available locale > names ? > How to use same locale names in every platform? There is no portable operating system interface to get the n

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

There is no portable operating system interface to get the names of all locales. Thank you. Why postgres does not use conditional directives like #ifdef WINDOWS ... #endif #ifdef LINUX .. #endif to return list of available locales ? Andrus. -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] A question about timezone

Good Afternoon select to_char(current_timestamp, 'Day') at time zone 'CET' produces Wednesday for Berlin Martin __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalm

[GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

I am having problems starting up Postgres and am getting the following error: 2010-12-01 08:36:42 EST LOG: database system was shut down at 2010-11-29 17:38:17 EST 2010-12-01 08:36:42 EST FATAL: invalid cache id: 19 2010-12-01 08:36:43 EST FATAL: the database system is starting up 2010-12-01 08

Re: [GENERAL] Does Postgres support bookmarks (tuples ids)?

On Wed, Dec 1, 2010 at 12:57 AM, Dann Corbit wrote: > The ctid is not permanent.   An alternative is to create tables with OID > values. Creating OIDs gives you zero benefit over having a PK. They would be roughly equivalent if you added a unique index on the OID column, but that is by no means

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy) wrote: > 2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a standalone > backend to vacuum that database. so did you do what the HINT told you to do? does it still not startup? > > I had multiple auto-vacuum instances running

[GENERAL] Grant command help -- postgres

Hi All - I created a schema , I used pg_restore with postgres as user and restored every thing to the new schema I created. Now I want to change the owner ship of the schema and all its objects to a different user other than postgres. Is it possible to do that with one command? Th

Re: [GENERAL] Stalled post to pgsql-general

REPOST! On Wed, Dec 1, 2010 at 8:34 PM, wrote: > Your message to pgsql-general has been delayed, and requires the approval > of the moderators, for the following reason(s): > > The author (Samba ) > is not a member of any of the restrict_post groups. > > If you do not wish the message to be pos

[GENERAL] Looking for auto starting procedures

Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( The othing thing is, that I need some internally r

Re: [GENERAL] Stalled post to pgsql-general

Samba writes: > REPOST! Please fix the subject line to be something useful when you resend a message. >> I'm trying to assign the individual sub arrays to other array elements >> like: >> >> other-array VARCHAR[]; >> >> other_array=some_array[1]; >> >> and I'm expecting to get '{samba,sarath,

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

Vick Khera writes: > On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy) > wrote: >> 2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a standalone >> backend to vacuum that database. > so did you do what the HINT told you to do? does it still not startup? This is the second r

[GENERAL] [REPOST] plpgsql : looping over multidimensional array : getting NULL for subdimension

Hi all, I'm trying to loop over a multidimensional array and find if any of the elements in a sub-dimensional array are matching some known criteria but facing issue with NULL for the sub arrays. I have a data structure that looks like: *some_array VARCHAR[][] := '{{samba,sarath,sreenivas},{s

Re: [GENERAL] Grant command help -- postgres

akp geek, > I created a schema , I used pg_restore with postgres as user and restored > every thing to the new schema I > created. Now I want to change the owner ship of the schema and all its > objects to a different user > other than postgres. Is it possible to do that with one command? In t

[GENERAL] plpgsql : looping over multidimensional array : getting NULL for subdimension

Hi all, I'm trying to loop over a multidimensional array and find if any of the elements in a sub-dimensional array are matching some known criteria but facing issue with NULL for the sub arrays. I have a data structure that looks like: *some_array VARCHAR[][] := '{{samba,sarath,sreeniv

[GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wednesday, December 01, 2010 9:58 AM Vick Khera wrote: > On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy) wrote: > > 2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a > > standalone backend to vacuum that database. > > so did you do what the HINT told you to do? does it

[GENERAL] How to recover a postgres installation from files

Hello. I have a not bootable Windows Server with Postgres 8.1, now I am trying to recover it but as I can't boot the windows, I can't start the postgres service and I can't do a "Backup" So, I only have the postgres directory on my hands. I decide to install the same database version in another c

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wed, Dec 1, 2010 at 11:23 AM, Jolles, Peter M (GE Energy) wrote: > EST WARNING:  database "fps_data" must be vacuumed within 1100 > transactions > > I know now that I'm going to have to slow down the transaction rate in order > to let the autovacuum keep up, but at this point I am just tr

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

On 1 Dec 2010, at 15:15, Andrus wrote: >> There is no portable operating system interface to get the names of all >> locales. > > Thank you. > Why postgres does not use conditional directives like > > #ifdef WINDOWS > ... > #endif > > #ifdef LINUX > .. > #endif > > to return list of available

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wednesday, December 01, 2010 1:27 PM Vick Khera wrote: > Just how big is the table and how many transactions per second do you push > through it? Perhaps your I/O bandwidth is just insufficient for your load > level. > The entire database is about 700 GB. In the database I've got approximatel

Re: [GENERAL] techniques for bulk load of spatial data

On 2010-11-30 14.29, Mario Corchero wrote: > Hi, I'm a student of Computer Science, > I know diffrents techniques of bulk load, but I need to know how > specifically postgreSQL make a bulk load of spatial data, could anyone If you are using spatial data in Postgres, this might usefully be addresse

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

the original hint said to "use a standalone backend". If you go to http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html section 23.1.4 it will describe exactly what just happened to you and how to recover. On Wed, Dec 1, 2010 at 1:58 PM, Jolles, Peter M (GE Energy) wrote: > Is

Re: [GENERAL] Grant command help -- postgres

Thanks for the help. it worked On Wed, Dec 1, 2010 at 11:10 AM, Gary Chambers wrote: > akp geek, > > > I created a schema , I used pg_restore with postgres as user and restored > every thing to the new schema I > > created. Now I want to change the owner ship of the schema and all its > objects

Re: [GENERAL] Does Postgres support bookmarks (tuples ids)?

On Wed, 2010-12-01 at 09:55 -0500, Vick Khera wrote: > On Wed, Dec 1, 2010 at 12:57 AM, Dann Corbit wrote: > > The ctid is not permanent. An alternative is to create tables with OID > > values. > > Creating OIDs gives you zero benefit over having a PK. They would be > roughly equivalent if you

[GENERAL] PG84 and SELinux

OS: CentOS-5.5 Earlier today I attempted to upgrade a production server from 8.1 to 8.4 using the pgdg-84-centos.repo. I say attempted because I could never get it to support ssl connections and as that is a requirement I had to roll back to 8.1. Whatever was the cause of the ssl problem I also

Re: [GENERAL] Warm Standby log filling up with "FATAL: the database system is starting up" entries

On Mittwoch, 1. Dezember 2010, Greg Swisher wrote: > FATAL: the database system is starting up This is what you get on 8.4, when you try to connect to a standby. Rgds, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.p

Re: [GENERAL] [REPOST] plpgsql : looping over multidimensional array : getting NULL for subdimension

On Wed, Dec 1, 2010 at 10:51 AM, Samba wrote: > Hi all, > > I'm trying to loop over a multidimensional array and find if any of the > elements in a sub-dimensional array are matching some known criteria but > facing issue with NULL for the sub arrays. > > I have a data structure that looks like: >

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

Alban, Seriously though, functionality like that has no place in a database server. That's the responsibility of the OS, or if it lacks in that respect, for third-party tools. Thank you. So only portable way is to probe Postgres 9 locales using CREATE DATABASE command with expected locale na

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wednesday, December 01, 2010 1:59 PM Vick Khera wrote: > the original hint said to "use a standalone backend". > > If you go to http://www.postgresql.org/docs/9.0/interactive/routine- > vacuuming.html > section 23.1.4 it will describe exactly what just happened to you and how to > recover. I a

[GENERAL] PG_ERROR 42501 permissions error

It never rains but it pours they say. I am trying to extract (ad hoc) some data from my production database on the 8.1 service that I tried to upgrade to 8.4. I am using the same username and password as is used by a process hosted on the same server as the posgresql instance. That process works

[GENERAL] Proper Permissions for /usr/local/pgsql/data

I'm manually replacing -8.3.3 with -9.0.1 I have the old data/ directory and pg_dumpall files safely stored. Following the directions at: , step #5 I see this: postg...@salmo:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql

Re: [GENERAL] PG84 and SELinux

"James B. Byrne" writes: > Earlier today I attempted to upgrade a production server from 8.1 to > 8.4 using the pgdg-84-centos.repo. I say attempted because I could > never get it to support ssl connections and as that is a requirement > I had to roll back to 8.1. Can't comment on that without a

Re: [GENERAL] PG_ERROR 42501 permissions error

"James B. Byrne" writes: > I am particularly nonplussed over the 'ERROR: 55P02: parameter > "standard_conforming_strings" cannot be changed' message. Can > anyone here clue me in as to what I am doing wrong? 8.1 did have a read-only parameter named that; in 8.2 and later it's a read-write param

Re: [GENERAL] Proper Permissions for /usr/local/pgsql/data

Hey Rich, You should chown your data dir (/usr/local/pgsql/data) to "postgres" before you run initdb. 2010/12/2 Rich Shepard > I'm manually replacing -8.3.3 with -9.0.1 I have the old data/ directory > and pg_dumpall files safely stored. Following the directions at: >

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

On 1 Dec 2010, at 21:46, Andrus wrote: > Alban, > >> Seriously though, functionality like that has no place in a database server. >> That's the responsibility of the OS, or if it lacks in that respect, for >> third-party tools. > > Thank you. > So only portable way is to probe Postgres 9 local

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wed, Dec 1, 2010 at 4:15 PM, Jolles, Peter M (GE Energy) wrote: > I apologize for the seeming dumb questions, but I have tried to start > the database using the following command: > > postgres --single -D "d:/database" mydatabase > > That gets me a new line in my command prompt window and nothi

Re: [GENERAL] Proper Permissions for /usr/local/pgsql/data

On Thu, 2 Dec 2010, Dmitriy Igrishin wrote: You should chown your data dir (/usr/local/pgsql/data) to "postgres" before you run initdb. Thank you, Dmitriy. I thought that was the proper owner but wanted to check first. This should be included in the document page on installation/upgrading. W

Re: [GENERAL] PG84 and SELinux

On Wed, December 1, 2010 16:54, Tom Lane wrote: >> Whatever was the cause of the ssl problem I also encountered a >> surprising number of SELinux violations. The following details >> the >> SELinux settings that I ultimately had to apply as a local module. >> This took a considerable period of t

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

"Jolles, Peter M (GE Energy)" writes: > I apologize for the seeming dumb questions, but I have tried to start > the database using the following command: > postgres --single -D "d:/database" mydatabase > That gets me a new line in my command prompt window and nothing else. Hmm ... try it with "

Re: [GENERAL] PG84 and SELinux

On Wed, December 1, 2010 16:54, Tom Lane wrote: > "James B. Byrne" writes: >> Earlier today I attempted to upgrade a production server from 8.1 >> to >> 8.4 using the pgdg-84-centos.repo. I say attempted because I >> could >> never get it to support ssl connections and as that is a >> requiremen

Re: [GENERAL] Looking for auto starting procedures

On 12/01/2010 11:16 PM, ma...@manfbraun.de wrote: The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use "auto-start stored procedures". Is there anything like this in postgresql, or what can I do? There is no built-in scheduler at pre

Re: [GENERAL] How to find correct locale name for CREATE DATABASE

On 12/02/2010 02:49 AM, Alban Hertroys wrote: Because, as opposed to the developers of the projects that you're apparently familiar with, the Postgres developers have common sense ;) Seriously though, functionality like that has no place in a database server. That's the responsibility of the

Re: [GENERAL] Proper Permissions for /usr/local/pgsql/data

On Wednesday 01 December 2010 3:01:29 pm Rich Shepard wrote: > On Thu, 2 Dec 2010, Dmitriy Igrishin wrote: > > You should chown your data dir (/usr/local/pgsql/data) to "postgres" > > before you run initdb. > >Thank you, Dmitriy. I thought that was the proper owner but wanted to > check first.

Re: [GENERAL] Cannot start Postgres- FATAL: invalid cache id: 19

On Wednesday 01 December 2010 1:15:45 pm Jolles, Peter M (GE Energy) wrote: > On Wednesday, December 01, 2010 1:59 PM Vick Khera wrote: > > the original hint said to "use a standalone backend". > > > > If you go to http://www.postgresql.org/docs/9.0/interactive/routine- > > vacuuming.html > > secti

[GENERAL] ResultSet problem in JDBC

Hi all, I am writing a Java program using postgres. When I run my program, I got the following error message: Exception in thread "main" org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.postgresql.jdbc4.Jdbc4ResultSet. Use setObject() with an explici

Re: [GENERAL] Does Postgres support bookmarks (tuples ids)?

Dann, it is a very good suggestion! How does OLEDB implement the bookmarks? Is there a specific Postgres feature that is used for the bookmarks? Thank you! Konstantin On Tue, Nov 30, 2010 at 11:13 PM, Dann Corbit wrote: > Is your application by chance using OLEDB? > > If that is the case, then

[GENERAL] number of rows returned by a query

Maybe this question was discussed previously, but I could not find an answer: An application used PQntuples function to obtain number of tuples after executing each query. Since number of rows increased we decided to use DECLARE CURSOR/FETCH commands to retrieve tuples. However, it seems that numb

Re: [GENERAL] [JDBC] ResultSet problem in JDBC

On 02/12/10 13:14, Zhipan Wang wrote: > Statement st = c.createStatement(); > ResultSet r = st.executeQuery("select unnest(col0) from relation2_col1 > where keyValue<500"); > PreparedStatement sql = c.prepareStatement( "SELECT col1 FROM relation2 > WHERE col0 IN ?"); > sql.setObject(1, r); > Resul

Re: [GENERAL] How to recover a postgres installation from files

On 02/12/10 01:11, Anibal David Acosta wrote: > So, I don't know how to recover the database from the data directory of a > windows postgres 8.1 installation. You'll need PostgreSQL 8.1 for Windows. This appears to have been removed from the FTP site for the well-intentioned reason that, on Windo