[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 not copied and getting error. pg_restore: [archiver (db)] Error from TOC entry 1550 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 can I upgrade from a lower major version to higher version? Should I first upgrade to version 8 first and then go for the 9? Please provide your suggestion regarding this. Regards~ Arun
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 can I upgrade from a lower major version to higher version? Should I first upgrade to version 8 first and then go for the 9? your problem is, you have data in your database that isn't valid UTF8. what encoding was the 7.4 system ? -- john r pierce 37N 122W somewhere on the middle of the left coast
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 pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 because deadlocks tend to be somewhat more problematic. Had I been involved with PostgreSQL at the time, I would have argued the value of staying with the standard serialization failure SQLSTATE (40001) for both, but it is unlikely to be changed at this point. From the application perspective, both can (and generally should) be treated as meaning that there was nothing wrong with the transaction in itself; it only failed because of conflicts with one or more concurrent transactions and is likely to succeed if retried from the start. Thank you very much for the prompt and informative reply! That clears up my doubt. For future reference: both 40001 and 40P01 are normal errors when issuing SERIALIZABLE transactions in a concurrent setting... Best, Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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, indexes, etc from that schema with out explicitly prefixing the schema name to those relations. Could anyone suggest what could be wrong with my setup and how to get past it? Thanks and Regards, Sumita -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839.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] Views
On Thu, May 9, 2013 at 4:33 PM, itishree sukla itishree.su...@gmail.comwrote: 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'; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Upgrading postgresql from version 7.4.3
2013/5/9 Arun P.L aru...@hotmail.com: 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 not copied and getting error. pg_restore: [archiver (db)] Error from TOC entry 1550 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 can I upgrade from a lower major version to higher version? Should I first upgrade to version 8 first and then go for the 9? Please provide your suggestion regarding this. The latest supported 8.x version is 8.4, which is quite a long way ahead of 7.4 in a whole number of ways. The gap between 8.4 and 9.2 is not quite so great, IMHO, so you might as well shoot for that. Providing you can solve the encoding problem, the largest potential issue you might face is the tightening of typecasting between 8.2 and 8.3: http://www.postgresql.org/docs/current/interactive/release-8-3.html#AEN124084 This is more something which will affect any applications which access your database and which rely on implicit casting. There are also some changes to PL/PgSQL which you will need to take into account if your database uses them. I suggest taking some time to go through the release notes. I have previously upgraded a 7.4 installation to 9.0 without too many problems; I did have to spend some time wading through the associated application code to isolate any potential casting issues. 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] 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 scott.marl...@gmail.com To: Tyson Maly tvm...@yahoo.com Cc: pgsql-general@postgresql.org 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 these updated? Once an hour, once a minute, once a second, a thousand times a second? If it's not more than once a second I would look at eager materialized views as a possibility for handing this. http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views#Eager_Materialized_View On Wed, May 8, 2013 at 7:45 AM, Tyson Maly tvm...@yahoo.com wrote: If I have a simple table with an id as a primary key that is a serial column and a column to keep track of a total_count for a particular id, what method would provide the fastest way to increment the total_count in the shortest amount of time and minimize any locking? id serial total_count integer Best regards, Ty -- To understand recursion, one must first understand recursion. -- 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] 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 same name. Even with these error messages, we are actually able to access tables, sequences, indexes, etc from that schema with out explicitly prefixing the schema name to those relations. Could anyone suggest what could be wrong with my setup and how to get past it? Thanks and Regards, Sumita -- 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] Does it make sense to break a large query into separate functions?
On Wed, May 8, 2013 at 4:47 PM, Gavin Flower gavinflo...@archidevsys.co.nz 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 part I think I know (but, I'm often wrong!). By saying it is immutable, you are saying that the values returned for the same set of parameters is always the same. The system looks at your immutable function that wraps random() and 'knows' that once invoked, the value returned will always be the same, so why would it want to invoke your immutable function multiple times, as it can just do that once and reuse the value returned? 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 stable wrapper doesn't exhibit the same behavior. In other words I'm pedantically chasing down the in-query planning differences between immutable and stable -- I don't think there should be any (but I certainly could be wrong about that). *) shouldn't a stable and immutable functions have the same semantics from query planning type of view? To me, immutable conveys all the guarantees of 'stable', plus the additional guarantee of function stability beyond the current transaction. merlin -- 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] Composite fields and the query planner
On 05/05/13 15:06, Tom Lane wrote: Steve Rogerson steve...@yewtc.demon.co.uk 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 (field1 type1, field2 varchar); create function get_part1(type1) returns varchar as $$ select ($1).part1 $$ language sql; create index i1 on table1 (get_part1(field1)); create index i2 on table1 (field2); I tested this example in HEAD and 9.0.x and didn't see any particular problem with rowcount estimates for the get_part1() expression. You do have to have the i1 index in place when the table is analyzed, else ANALYZE won't collect any stats about the expression. regards, tom lane In the end it turned out to be another issue. As best as I can see, what happened was that if the value of field2 was not in the common values list, then it would use the two indexes, otherwise it would use one index and filter by field2. It takes about 10 times longer to get the two list and AND them than doing one and filtering. I think we've found a work around, using a combined index. Having said all that I did discover an unrelated problem with composite fields. Basically it doesn't seem to consult the pg_stats data to decide if to do a seq scan or an index scan. Always saying that it should do an index scan. I'm not quite sure how it could compare the value that is indexed with the pg_stats data, so I guess it either decides it's all less than or all greater than the value you are checking for. Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson
Re: [GENERAL] Does it make sense to break a large query into separate functions?
Merlin Moncure mmonc...@gmail.com 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 stable wrapper doesn't exhibit the same behavior. 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-fold it would not be taken. It's conceivable that we could somehow decorate the parsetree so that the function call node for to_date() would still be considered immutable because it came out of inlining an immutable function. But that's not how things work now, and I'm dubious that it'd be worth the trouble. Really the right answer here is to label the wrapper with the same mutability level as its contents. 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] 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
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 t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com 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 stable wrapper doesn't exhibit the same behavior. 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-fold it would not be taken. But the stable decorated function *is* inlined. I read your reasoning a couple of times above why decorating the function immutable (as opposed to stable) defeats inlining, but I'm having trouble parsing it. It's not clear to me why stable and immutable functions are treated differently at all at plan time (regardless of decoration, assuming we are not talking about volatile functions as non-volatile). It's conceivable that we could somehow decorate the parsetree so that the function call node for to_date() would still be considered immutable because it came out of inlining an immutable function. But that's not how things work now, and I'm dubious that it'd be worth the trouble. Really the right answer here is to label the wrapper with the same mutability level as its contents. That's a fair point, but i'd state that to_date/ *is* immutable in this context and abstracting the functionality into a immutable function seems reasonable (say, so that you could use the same function in a functional index). Having to keep an alternate version (marked stable) in order to preserve inlining seems ... awkward. merlin -- 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] 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Does it make sense to break a large query into separate functions?
Merlin Moncure mmonc...@gmail.com writes:
On Thu, May 9, 2013 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us 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-fold it would not be taken.
But the stable decorated function *is* inlined.
Right, because then the perceived volatility of the expression isn't
increasing.
I read your reasoning
a couple of times above why decorating the function immutable (as
opposed to stable) defeats inlining, but I'm having trouble parsing
it. It's not clear to me why stable and immutable functions are
treated differently at all at plan time (regardless of decoration,
assuming we are not talking about volatile functions as non-volatile).
foo('1923') will be folded to a constant at plan time.
to_date('1923','') will not be. That's the difference so far
as the planner is concerned. You can verify this with EXPLAIN VERBOSE.
The rule about not increasing the volatility of an expression is mainly
meant to prevent inlining a stable/immutable function that actually
contains a volatile-labeled function, because doing that would have
significantly greater consequences, eg not being able to use the
expression in an indexqual. But it's coded as don't increase the
volatility at all. I'm not sure whether there would be severe
consequences if we allowed the immutable-to-stable case. It's at
least possible that we'd end up missing some constant-folding
opportunities, depending on the order of operations in the planner.
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] 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 lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
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. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto: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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
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-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto: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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net /Cuda Systems LLC/ Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data. -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. I think you're better off with PQexecParams() so that you don't have to encode the image at all; just load it in memory and use it as a parameter. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] 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-get-length-of(badge_binary_data); /* You have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. I think you're better off with PQexecParams() so that you don't have to encode the image at all; just load it in memory and use it as a parameter. Yeah, you can go that route too. -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
[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/bin 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 presence of required libraries ok Checking database user is a superuser ok *Only the install user can be defined in the new cluster.* *Failure, exiting* *Can anyone help me in this regard..* -- Regards, Ramesh Eslavath
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.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. Thanks Achilleas. I usually do the physical design in vi using sql scripts, and I like to include a couple of inserts and selects to make sure everything is going according to plan. It looks like I may just have to work with a front-end developer for this particular instance. Of all the stupid things, in all of my years doing this I've never once had to work with storing binary files, other than years ago when I was studying for some of the MySQL certs. If I do happen to come up with a solution that works I will be sure to post it here. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
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 k...@denninger.net 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://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 lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net *Cuda Systems LLC* Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table
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=/var/lib/pgsql/9.2/data --old-bindir=/usr/pgsql-9.1/bin --new-bindir=/usr/pgsql-9.2/bin 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 presence of required libraries ok Checking database user is a superuser ok Only the install user can be defined in the new cluster. Failure, exiting Can anyone help me in this regard.. You must only have the install user defined in the new cluster. Either they are not defiend, or a second user is also defined. If you execute 'SELECT * from pg_authid' in the new cluster, I think you will see the problem. Our C comments say: * We only allow the install user in the new cluster because other * defined users might match users defined in the old cluster and * generate an error during pg_dump restore. Let me know what you find in case I can improve the error message. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Storing small image files
2013/5/9 Nelson Green nelsongree...@gmail.com 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 k...@denninger.netwrote: 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-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net *Cuda Systems LLC* Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and
Re: [GENERAL] Storing small image files
On Thu, May 9, 2013 at 12:05 PM, Nelson Green nelsongree...@gmail.comwrote:
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.html
didnt try it myself tho.
Most of the time people manipulate bytea's using a higher level
programming lang.
Thanks Achilleas. I usually do the physical design in vi using sql
scripts, and I like to include a couple of inserts and selects to make sure
everything is going according to plan. It looks like I may just have to
work with a front-end developer for this particular instance. Of all the
stupid things, in all of my years doing this I've never once had to work
with storing binary files, other than years ago when I was studying for
some of the MySQL certs.
If I do happen to come up with a solution that works I will be sure to
post it here.
OK, this is kind of convoluted, but I got a couple of test cases that work
for me. The steps to make the first one are below.
First I took one of the photos and shrunk it real small using GIMP. Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 test.jpg test.64
this created a 113 line base64 file. I then just put those 113 lines into
my insert statement:
INSERT INTO security_badge
VALUES
(
'PK00880918',
(SELECT employee_id
FROM employee
WHERE employee_lastname = 'Kendell' AND
employee_firstname = 'Paul'),
decode('all 113 lines of the base64 string manually pasted from
test.64','base64')
);
Then to retrieve the file:
\o /output.64
SELECT ENCODE((SELECT badge_photo
FROM security_badge
WHERE badge_number = 'PK00880918'),'BASE64');
That outputs a base64 string that matches test.64. Outputting that to a
file and then converting it back gives me my image:
/usr/bin/base64 -d output.64 newtest.jpg
Like I said, kind of crazy, but it satisfies me that my basic premise is
doable. I'll still get one of the front-end developers to whip out some PHP
just to be safe.
Thanks to all!
On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
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 psql client.
Let's say I have an employee named Paul Kendell, who's employee ID is
880918. Their badge number will be PK00880918, and their badge photo is
named /tmp/PK00880918.jpg. What would the INSERT statement look like to put
that information into the security_badge table, and what would the SELECT
statement look like to retrieve that record?
Thanks for your time.
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 Μαΐ 2013 10:04:18 Nelson Green wrote:
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 having trouble
understanding just how to do that.
The table structures:
CREATE TABLE employee
(
employee_idINTEGER NOT NULL,
employee_lastname VARCHAR(35) NOT NULL,
employee_firstname VARCHAR(35) NOT NULL,
employee_miCHAR(1),
PRIMARY KEY (employee_id)
);
CREATE TABLE security_badge
(
badge_number CHAR(10)NOT NULL,
employee_idINTEGER NOT NULL
REFERENCES employee(employee_id),
badge_photo,
PRIMARY KEY (badge_number)
);
What datatype should I use for the badge_photo (bytea?), and what are the
commands to insert the picture accessing the server remotely through psql,
and to retrieve the photos as well, please?
Thanks,
Nelson
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
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, May 9, 2013 at 12:31 PM, Misa Simic misa.si...@gmail.com wrote: 2013/5/9 Nelson Green nelsongree...@gmail.com 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 k...@denninger.netwrote: 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-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net *Cuda Systems LLC* Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine.
Re: [GENERAL] Storing small image files
2013/5/9 Nelson Green nelsongree...@gmail.com 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, May 9, 2013 at 12:31 PM, Misa Simic misa.si...@gmail.com wrote: 2013/5/9 Nelson Green nelsongree...@gmail.com 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 k...@denninger.netwrote: 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-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net *Cuda Systems LLC* Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars
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 Thu, May 9, 2013 at 1:49 PM, Misa Simic misa.si...@gmail.com wrote: 2013/5/9 Nelson Green nelsongree...@gmail.com 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, May 9, 2013 at 12:31 PM, Misa Simic misa.si...@gmail.com wrote: 2013/5/9 Nelson Green nelsongree...@gmail.com 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 k...@denninger.netwrote: 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-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: 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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net *Cuda Systems LLC* Oops -- forgot the second
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 k...@denninger.net mailto:k...@denninger.net 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://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 lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto: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 psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. 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 Μαΐ 2013 10:04:18 Nelson Green wrote: 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 having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt 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 have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net mailto:k...@denninger.net /Cuda Systems LLC/ Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how
Re: [GENERAL] LONG delete with LOTS of FK's
On 2013-05-02 10:08, Tom Lane wrote: Larry Rosenman l...@lerctr.org 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 you could check for yourself by doing something like explain select 1 from table where account_id = something and verifying that you get an indexscan not a seqscan, for each dependent table. regards, tom lane even having done that, and with a SMALL list, we still are getting VERY LONG deletes: druckerdb= select * from pg_stat_activity where procpid=17795; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query ---+---+-+--+-+--+-+-+-+---+---+--- +-+ --- 16407 | druckerdb | 17795 |16385 | drucker | | 192.168.100.230 | blueprint-app1 | 44191 | 2013-05-09 15:07:56.070267-05 | 2013-05-09 15:07:59.14-05 | 2013-05-09 15:08:20.59 7237-05 | f | DELETE FROM account WHERE id IN (248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,48 6866944,692846592) (1 row) druckerdb= \d account Table public.account Column | Type | Modifiers ---+--+--- id| bigint | not null name | character varying(64)| not null value_table_name | character varying(32)| not null version_item_id | bigint | not null blob_table_name | character varying(32)| not null default 'blobs'::character varying account_type | smallint | not null default 1 account_status| smallint | not null default 1 editor_licenses | integer | not null default 1 expire_date | date | appserver | text | not null default 'appserver1'::text appport | text | not null default '8080'::text file_space| bigint | security_policy | integer | not null expiry_processed | boolean | default false contributor_licenses | integer | not null default 0 valid_invite_email_domains| character varying| allow_api_calls | boolean | default false allow_chat| boolean | default true is_template_account | boolean | not null default false billing_type | integer | not null default 0 epayment_profile_id | text | not null instance_value_table_name | character varying(32)| not null default 'instance_values'::character varying show_public_stream| boolean | not null enable_posting| boolean | default true cbn_type | smallint | not null default 0 account_roles | integer | not null sap_id| text | performance_logging | smallint | default 4 admins_access_glossary_and_all_spaces | boolean | not null default false signup_country_code | text | viewer_licenses | integer | not null default 0 glossary_id | bigint | terms_of_use_version_accepted | integer | terms_of_use_accepted_date| timestamp with time zone | terms_of_use_admin_id | bigint |
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 l...@lerctr.org 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 you could check for yourself by doing something like explain select 1 from table where account_id = something and verifying that you get an indexscan not a seqscan, for each dependent table. regards, tom lane even having done that, and with a SMALL list, we still are getting VERY LONG deletes: druckerdb= select * from pg_stat_activity where procpid=17795; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query ---+---+-+--+-+--+-+-+-+---+---+--- +-+ --- 16407 | druckerdb | 17795 |16385 | drucker | | 192.168.100.230 | blueprint-app1 | 44191 | 2013-05-09 15:07:56.070267-05 | 2013-05-09 15:07:59.14-05 | 2013-05-09 15:08:20.59 7237-05 | f | DELETE FROM account WHERE id IN (248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,48 6866944,692846592) (1 row) druckerdb= \d account Table public.account Column | Type | Modifiers ---+--+--- id| bigint | not null name | character varying(64)| not null value_table_name | character varying(32)| not null version_item_id | bigint | not null blob_table_name | character varying(32)| not null default 'blobs'::character varying account_type | smallint | not null default 1 account_status| smallint | not null default 1 editor_licenses | integer | not null default 1 expire_date | date | appserver | text | not null default 'appserver1'::text appport | text | not null default '8080'::text file_space| bigint | security_policy | integer | not null expiry_processed | boolean | default false contributor_licenses | integer | not null default 0 valid_invite_email_domains| character varying| allow_api_calls | boolean | default false allow_chat| boolean | default true is_template_account | boolean | not null default false billing_type | integer | not null default 0 epayment_profile_id | text | not null instance_value_table_name | character varying(32)| not null default 'instance_values'::character varying show_public_stream| boolean | not null enable_posting| boolean | default true cbn_type | smallint | not null default 0 account_roles | integer | not null sap_id| text | performance_logging | smallint | default 4 admins_access_glossary_and_all_spaces | boolean | not null default false signup_country_code | text | viewer_licenses | integer | not null default 0 glossary_id | bigint | terms_of_use_version_accepted | integer | terms_of_use_accepted_date| timestamp with time zone | terms_of_use_admin_id | bigint | terms_of_use_accepted_by_id | bigint |
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 you cancel. Something tells me that if you try this again, it'll be the same foreign key check. Look and make sure account_id in ibmgbs_values is the same exact type as the referenced table. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] LONG delete with LOTS of FK's
Larry Rosenman l...@lerctr.org 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) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 because of the statement that you see when you cancel. Something tells me that if you try this again, it'll be the same foreign key check. Look and make sure account_id in ibmgbs_values is the same exact type as the referenced table. it is: druckerdb= \d ibmgbs_values Table public.ibmgbs_values Column |Type | Modifiers ---+-+--- account_id| bigint | not null All are bigints. It's been on various tables, and they are all bigints. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- 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] LONG delete with LOTS of FK's
On 2013-05-09 16:22, Tom Lane wrote: Larry Rosenman l...@lerctr.org 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 was consuming CPU and doing I/O (D-S-D state), etc. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- 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] 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 'IDLE in transaction' statements, or your long delete is marked as waiting in pg_stat_activity, something is up. I've also found this query extremely helpful in tracking down things like this: SELECT DISTINCT l1.pid AS blocker_pid, a.current_query AS blocker_query, a.usename AS blocker_user, a.client_addr AS blocker_client, l2.pid AS blocked_pid, a2.current_query AS blocked_query, a2.usename AS blocked_user, a2.client_addr AS blocked_client FROM pg_locks l1 JOIN pg_stat_activity() a on (a.procpid = l1.pid) JOIN pg_locks l2 ON (l1.relation = l2.relation AND l1.pid != l2.pid) JOIN pg_stat_activity() a2 on (a2.procpid = l2.pid) WHERE l1.granted AND NOT l2.granted; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] LONG delete with LOTS of FK's
Larry Rosenman l...@lerctr.org 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. Hm. I'm suspicious that you still ended up with a seqscan checking plan. Was this session started after you added all the missing indexes? If not, it seems possible that it was using a bad pre-cached plan. 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] LONG delete with LOTS of FK's
On 2013-05-09 16:40, Tom Lane wrote: Larry Rosenman l...@lerctr.org 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. Hm. I'm suspicious that you still ended up with a seqscan checking plan. Was this session started after you added all the missing indexes? If not, it seems possible that it was using a bad pre-cached plan. regards, tom lane I added the indexes on last friday, and we've done a number of vacuumdb -zav's (every night) since then. So, if there's a cached plan, it's not from me. (we also restarted our app on Saturday night). -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- 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] 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 relationship model. It is good to hear but when a simple query requesting 20 records takes nearly 7 minutes to complete, it becomes nearly unusable. Hi, can you reply to the list? This is a performance question now. You might want to start a new thread on your performance issues. Have you utilized indexes? (refer to Hash Cond) Hash Cond: ((rauthor.rart_id)::text = (ritem.ut)::text) look at actual time=start..finish on the planner process blocks and also the finish time of the preceding block. Buffers: shared hit=104662 read=4745067, temp read=1006508 written=1006446 Give the specs of your hardware. More experience people will be able to advise on getting the best out of this query. Once you are reasonably happy with your schema you might want to consider partitioning the larger datasets. You can also cache (temp) blocks of data how you see appropriate (snapshots). http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html Regards. Julian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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. Any ideas? Brett -- 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] 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? which version of OS X? All of these factors will help isolate the root of the problem. Dru On May 9, 2013, at 8:37 PM, Brett Haydon br...@haydon.id.au wrote: 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. Any ideas? Brett -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] 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 altogether brett=# select 1+1;select 1+2; On 10/05/2013, at 10:48 AM, Andrew Satori d...@druware.com wrote: 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? which version of OS X? All of these factors will help isolate the root of the problem. Dru On May 9, 2013, at 8:37 PM, Brett Haydon br...@haydon.id.au wrote: 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. Any ideas? Brett -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] 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 an easy fix. On May 9, 2013, at 9:01 PM, Brett Haydon br...@haydon.id.au wrote: 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 altogether brett=# select 1+1;select 1+2; On 10/05/2013, at 10:48 AM, Andrew Satori d...@druware.com wrote: 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? which version of OS X? All of these factors will help isolate the root of the problem. Dru On May 9, 2013, at 8:37 PM, Brett Haydon br...@haydon.id.au wrote: 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. Any ideas? Brett -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] psql history on OSX terminal
Brett Haydon br...@haydon.id.au 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 nuts. Google was not my friend. Any ideas? What that sounds like is that you're running psql with the wrong value of the TERM environment variable, so that it's guessing wrong about what control characters to send for terminal cursor positioning. Try echo $TERM and see what you get. On my Mac laptop, it seems to default to xterm and I get good results from that. 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] 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 t...@sss.pgh.pa.us wrote: Brett Haydon br...@haydon.id.au 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 nuts. Google was not my friend. Any ideas? What that sounds like is that you're running psql with the wrong value of the TERM environment variable, so that it's guessing wrong about what control characters to send for terminal cursor positioning. Try echo $TERM and see what you get. On my Mac laptop, it seems to default to xterm and I get good results from that. 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
[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 typecasting issues and I am on it now. Thank you very much for your suggestions...
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 issues and I am on it now. Thank you very much for your suggestions...
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 encoding, it just treats it as bytes. any values could be stored, and its quite likely that they aren't UTF8 compatible -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] 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 aru...@hotmail.com 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 encoding right now, then don't. 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] Upgrading postgresql from version 7.4.3
On Thu, May 9, 2013 at 9:41 PM, Arun P.L aru...@hotmail.com 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 bad thing depends on circumstances (it's usually not desirable) but in your case it may be a good thing. Best Wishes, Chris Travers Regards~ Arun
