[GENERAL] Upgrading postgresql from version 7.4.3

Hi, How can I upgrade to latest postgresql version 9.2 from my current version 7.4.3? How complicated this will be as the db contains large amount of data? I have installed the latest version 9.2 in new server and while restoring the dump from old version, data in some of the large tables are n

Re: [GENERAL] Upgrading postgresql from version 7.4.3

On 5/9/2013 2:02 AM, Arun P.L wrote: pg_restore: [archiver (db)] Error from TOC entry1550 TABLE DATA *table_name*; pg_restore: [archiver (db)] COPY failed for table "*table**_name*": ERROR: invalid byte sequence for encoding "UTF8": 0xa0 What else can I do for this issue, or in general how

Re: [GENERAL] Normal errors codes in serializable transactions

Hi, > 40P01 is mentioned in the manual. See "A. PostgreSQL Error Codes" of > Appendixes. I meant "mentioned in the manual in the section about concurrency control". Since I alluded to class 40 errors, I think it was safe to assume that I was familiar with Appendix A... Best, Jon -- Sent via

Re: [GENERAL] Normal errors codes in serializable transactions

Hi, > In PostgreSQL, 40001 is used for serialization failures due to MVCC > issues, and 40P01 is used for serialization failures due to > deadlocks.  I think that many years back when PostgreSQL moved to > MVCC it was judged important to differentiate between them with > different SQLSTATE values

[GENERAL] Views

Hi all, Is there any way, i can know what all views are there on a table by a sql query? Regards, Itishree

[GENERAL] FATAL: database "a/system_data" does not exist

Hi All, I am observing some weird errors in the postgres logs after upgrading to Postgres 9.2.4. FATAL: database "a/system_data" does not exist We do have a user named "system_data" and a schema with the same name. Even with these error messages, we are actually able to access tables, sequences,

Re: [GENERAL] Views

On Thu, May 9, 2013 at 4:33 PM, itishree sukla wrote: > Hi all, > > Is there any way, i can know what all views are there on a table by a sql > query? > > > Regards, > Itishree > Try this... select table_name,view_name from information_schema.view_table_usage where table_name='table_name'; ---

Re: [GENERAL] Upgrading postgresql from version 7.4.3

2013/5/9 Arun P.L : > Hi, > > How can I upgrade to latest postgresql version 9.2 from my current version > 7.4.3? How complicated this will be as the db contains large amount of data? > I have installed the latest version 9.2 in new server and while restoring > the dump from old version, data in so

Re: [GENERAL] question on most efficient way to increment a column

In some cases, it would be 2-10 times a second per id. From: Scott Marlowe To: Tyson Maly Cc: "pgsql-general@postgresql.org" Sent: Wednesday, May 8, 2013 10:10 PM Subject: Re: [GENERAL] question on most efficient way to increment a column How often are

Re: [GENERAL] FATAL: database "a/system_data" does not exist

On 05/09/2013 04:28 AM, sumita wrote: Hi All, I am observing some weird errors in the postgres logs after upgrading to Postgres 9.2.4. FATAL: database "a/system_data" does not exist What else happens right before the above message? We do have a user named "system_data" and a schema with the

Re: [GENERAL] Does it make sense to break a large query into separate functions?

On Wed, May 8, 2013 at 4:47 PM, Gavin Flower wrote: > On 09/05/13 09:35, Merlin Moncure wrote: > [...] > > >> More oddness -- when I wrap, say, random() with stable function, I get >> unique value per returned row, but same value across the set when wrapped >> with immutable. > > [..] > > That par

Re: [GENERAL] Composite fields and the query planner

On 05/05/13 15:06, Tom Lane wrote: Steve Rogerson writes: I'm seeing a problem with the query planner not doing what's expected, and I think it is because we are using composite fields. Here is a stripped down example. create type type1 as ( part1 varchar, part2 varchar); create table table1 (f

[GENERAL] Storing small image files

Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am ha

Re: [GENERAL] Does it make sense to break a large query into separate functions?

Merlin Moncure writes: > right. note I think that (reduced invocation of immutable function) is > the correct behavior.If I say something is immutable, rightly or > wrongly, I am giving postgres a free hand to reduce function calls. > But (wandering off topic here), But I'm wondering why the

Re: [GENERAL] Storing small image files

why not bytea? much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 20

Re: [GENERAL] Does it make sense to break a large query into separate functions?

On Thu, May 9, 2013 at 10:15 AM, Tom Lane wrote: > Merlin Moncure writes: >> right. note I think that (reduced invocation of immutable function) is >> the correct behavior.If I say something is immutable, rightly or >> wrongly, I am giving postgres a free hand to reduce function calls. >> But

Re: [GENERAL] Storing small image files

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > ** > > why not bytea? > Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the

Re: [GENERAL] Does it make sense to break a large query into separate functions?

Merlin Moncure writes: > On Thu, May 9, 2013 at 10:15 AM, Tom Lane wrote: >> The reason it won't inline such a wrapper is that if it expands foo(x) >> to to_date(x, ''), the latter expression will be considered stable, >> not immutable; so for example any subsequent opportunity to >> constant

Re: [GENERAL] Storing small image files

Take a look here first : http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lan

Re: [GENERAL] Storing small image files

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: > > Take a look here first : > > http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html > > > > then here : > http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html > > > > didnt try it myself tho. > > >

Re: [GENERAL] Storing small image files

On 5/9/2013 11:12 AM, Karl Denninger wrote: > On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: >> >> Take a look here first : >> >> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html >> >> >> >> then here : >> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea

Re: [GENERAL] Storing small image files

Karl Denninger escribió: > > To encode: > > > > > > write_conn = Postgresql communication channel in your software that is > > open to write to the table > > > > char*out; > > size_tout_length, badge_length; > > > > badge_length = function-to-get-length-of(badge_binary_data); /* You > > h

Re: [GENERAL] Storing small image files

On 5/9/2013 11:34 AM, Alvaro Herrera wrote: > Karl Denninger escribió: > >>> To encode: >>> >>> >>> write_conn = Postgresql communication channel in your software that is >>> open to write to the table >>> >>> char*out; >>> size_tout_length, badge_length; >>> >>> badge_length = function-to-

[GENERAL] pg_upgrade error

hello there, we are getting struck with this error while upgrading while upgrading from 9.1 to 9.2 -bash-4.1$ clear -bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade -c --old-datadir=/var/lib/pgsql/9.1/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/pgsql-9.1/bin --new-bindir=/usr/pgsql-9.2

Re: [GENERAL] Storing small image files

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > ** > > Take a look here first : > > http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html > > > > then here : > http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.

Re: [GENERAL] Storing small image files

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I? On Thu, May 9, 2013 at 11:21 AM, Karl Denninger wrote: > On 5/9/2013 11:12 AM, Karl Denninger wrote: > > On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: > > Take a look here first : > > http://w

Re: [GENERAL] pg_upgrade error

On Thu, May 9, 2013 at 06:16:31PM +0530, Ramesh naik wrote: > hello there, > > we are getting struck with this error while upgrading while upgrading from 9.1 > to 9.2 > > > -bash-4.1$ clear > -bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade -c --old-datadir=/var/lib/pgsql/9.1/ > data --new-datadir=/va

Re: [GENERAL] Storing small image files

2013/5/9 Nelson Green > Thanks Karl, but I'm trying to do this from a psql shell. I can't use the > C functions there, can I? > > > On Thu, May 9, 2013 at 11:21 AM, Karl Denninger wrote: > >> On 5/9/2013 11:12 AM, Karl Denninger wrote: >> >> On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: >> >>

Re: [GENERAL] Storing small image files

On Thu, May 9, 2013 at 12:05 PM, Nelson Green wrote: > > > > On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> wrote: > >> ** >> >> Take a look here first : >> >> http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html >> >> >> >> then here : >> http

Re: [GENERAL] Storing small image files

Hi Misa, That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need. Thanks! On Thu, M

Re: [GENERAL] Storing small image files

2013/5/9 Nelson Green > Hi Misa, > > That gives me a "ERROR: must be superuser to use server-side lo_import()" > on the client. I think this is enforced to preserve file permissions on the > server? I appreciate the suggestion, and I will keep it, but I think I > found a solution that meets my i

Re: [GENERAL] Storing small image files

Thanks Misa, for confirming my suspicions about server permissions. Like I said, what I came up will work for my simple needs. I have a script that creates the table, inserts a test row, and successfully retrieves it, which is all I need at this point. I appreciate all the help from everyone. On

Re: [GENERAL] Storing small image files

On 5/9/2013 12:08 PM, Nelson Green wrote: > Thanks Karl, but I'm trying to do this from a psql shell. I can't use > the C functions there, can I? > > > On Thu, May 9, 2013 at 11:21 AM, Karl Denninger > wrote: > > On 5/9/2013 11:12 AM, Karl Denninger wrote: >> On

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman writes: Question: Do all these need to have a bare index just on the account_id column, or is a multicolumn index with account_id first sufficient for the check to be reasonably quick? I would think that such an index would be sufficient, but

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 15:50, Larry Rosenman wrote: On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman writes: Question: Do all these need to have a bare index just on the account_id column, or is a multicolumn index with account_id first sufficient for the check to be reasonably quick? I would think t

Re: [GENERAL] LONG delete with LOTS of FK's

On 05/09/2013 03:58 PM, Larry Rosenman wrote: "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" This is the statement it canceled on. I've found that when long deletes like this happen, it's because of the statement that you see when y

Re: [GENERAL] LONG delete with LOTS of FK's

Larry Rosenman writes: >> Ideas on how to debug? Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:20, Shaun Thomas wrote: On 05/09/2013 03:58 PM, Larry Rosenman wrote: "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" This is the statement it canceled on. I've found that when long deletes like this happen, it's becau

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:22, Tom Lane wrote: Larry Rosenman writes: Ideas on how to debug? Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? regards, tom lane all the locks were clear, and it

Re: [GENERAL] LONG delete with LOTS of FK's

On 05/09/2013 04:22 PM, Larry Rosenman wrote: It's been on various tables, and they are all bigints. Hey, ya never know. I've gotten tripped up similarly. In that case, I defer to Tom's suggestion. If there are any ' in transaction' statements, or your long delete is marked as waiting in pg

Re: [GENERAL] LONG delete with LOTS of FK's

Larry Rosenman writes: > On 2013-05-09 16:22, Tom Lane wrote: >> Perhaps it's blocked on a lock? Did you look into pg_locks? >> Did you note whether the process was consuming CPU time and/or doing >> IO? > all the locks were clear, and it was consuming CPU and doing I/O > (D->S->D state), etc.

Re: [GENERAL] LONG delete with LOTS of FK's

On 2013-05-09 16:40, Tom Lane wrote: Larry Rosenman writes: On 2013-05-09 16:22, Tom Lane wrote: Perhaps it's blocked on a lock? Did you look into pg_locks? Did you note whether the process was consuming CPU time and/or doing IO? all the locks were clear, and it was consuming CPU and doing I/O

Re: [GENERAL] Design advice requested

On 09/05/13 17:42, Johann Spies wrote: > Hallo Julian, > > Thanks for your reply. > > Firstly, don't worry too much about speed in the design phase, > there may > be differences of opinion here, but mine is that even with database > design the first fundamental layer is the relation

[GENERAL] psql history on OSX terminal

On OSX terminal, when I try and access psql history the historical line is only partially visible sometimes mixed with the last line executed, and the start position of the cursor moves about 15-20 chars in. The line still executes correctly, but it's driving me nuts. Google was not my friend. A

Re: [GENERAL] psql history on OSX terminal

Several, but most depend upon how you installed. In several of the installation methods (binary) the default terminal management routine has some issues (version linked to), but there are several other possible interactions. Which way did you install? which shell are you using in terminal? wh

Re: [GENERAL] psql history on OSX terminal

Using the Heroku PostresApp (9.2.2) /Applications/Postgres.app/Contents/MacOS/bin/psql OSX 10.8 default bash shell This is kinda what I end up with (one line after the other and the cursor in the middle) though using the down arrow key can add extra lines in completely messing up the line altog

Re: [GENERAL] psql history on OSX terminal

While I am not all *that* familiar with the Heroku version, it looks a bit like they are are linking against libreadline instead of libedit in order to maximize platform compatability with older revisions of OS X (10.6.x). Short of rebuilding against libedit on 10.8 I don't know that you have a

Re: [GENERAL] psql history on OSX terminal

Brett Haydon writes: > On OSX terminal, when I try and access psql history the historical line is > only partially visible sometimes mixed with the last line executed, and the > start position of the cursor moves about 15-20 chars in. The line still > executes correctly, but it's driving me nut

Re: [GENERAL] psql history on OSX terminal

Turns out it was an errant line in a my psqlrc file that customised the prompt.. doh. Thanks anyway. On 10/05/2013, at 1:17 PM, Tom Lane wrote: > Brett Haydon writes: >> On OSX terminal, when I try and access psql history the historical line is >> only partially visible sometimes mixed with

[GENERAL] Re: [GENERAL] Upgrading postgresql from version 7.4.3‏

Since there was some issue with hotmail I couldn't reply earlier. Encoding in version 7.4 was SQL_ASCII. What's the best way to deal with this? I hope changing the encoding to UTF-8 while taking dump will work. Surely I will have an eye on release notes in order to deal with the t

Re: [GENERAL] Upgrading postgresql from version 7.4.3

Since there was some issue with hotmail I couldn't reply earlier. Encoding in version 7.4 was SQL_ASCII. What's the best way to deal with this? I hope changing the encoding to UTF-8 while taking dump will work. Surely I will have an eye on release notes in order to deal with the typecasting is

Re: [GENERAL] Upgrading postgresql from version 7.4.3

On 5/9/2013 9:12 PM, Arun P.L wrote: Since there was some issue with hotmail I couldn't reply earlier. Encoding in version 7.4 was SQL_ASCII. What's the best way to deal with this? I hope changing the encoding to UTF-8 while taking dump will work. SQL_ASCII means that postgres has no encodin

Re: [GENERAL] Upgrading postgresql from version 7.4.3

Hi, And the way to deal with this? Any ideas on that? Whether changing encoding to utf-8 in dump work? Regards~ Arun

Re: [GENERAL] Upgrading postgresql from version 7.4.3

"Arun P.L" writes: > And the way to deal with this? Any ideas on that? If you were happy with SQL_ASCII encoding before, keep using it. Other encoding choices are basically constraints to ensure your data is valid according to that encoding --- if you don't feel like trying to clean up the data e

Re: [GENERAL] Upgrading postgresql from version 7.4.3

On Thu, May 9, 2013 at 9:41 PM, Arun P.L wrote: > Hi, > > And the way to deal with this? Any ideas on that? Whether changing > encoding to utf-8 in dump work? > Yes, run initdb -E SQL_ASCII This will create your database cluster with no encoding restrictions. Whether this is a good thing or a