Re: [GENERAL] Mixed client_encoding database ques tion
Hi, I suggest you to recreate database in WIN1521 encoding, so Java will not write unsupported characters. As well you can configure JDBC in way it will allow You to change client encoding, so you can change it to WIN. In both of above You will get error when you will try to write unsupported characters. You may also do http://www.postgresql.org/docs/8.4/interactive/sql-createconversion.html Radosław Smogura, http://softperience.eu On Mon, 10 Jan 2011 16:32:13 +0100, J. Hondius wrote: Hi all, We have a product with a PostgreSQLdatabase (unicode) It has a (legacy) client app that uses client_encoding = win1521 The legacy app will be around for several years. It cannot change to unicode. There also is a new client app, written in java. It uses client_encoding = unicode. The problem: if someone inputs a non-win1252 character via the new java app, the legacy app fails. PostgreSQL returns an error: character blabla of encodig UTF-8 has no equivalent in WIN1252. What are my options to stop the legacy app from failing? -Is there a setting to tell PostgreSQL to fail the character gracefully by substuting it? -Should i filter out all non win1252 charachters at the new java app and substitute them? (not so nice, i'd like to have the chars in the db) -?more? Greetings, Joek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query cancellation in JDBC
Hi, I done in JDBC driver barrier preventing of execution of query until the cancel will not return, but future queries after cancellation are occasionally terminated. The query cancel sends following 16, 123456789, pid, key, flush, after this it tries to read EOF, I got and ignore exception "Connection reset" - probably OK, so query cancel finishes. The next query will not be executed until cancellation will end. I think problem can be in backend, it can signal process but it doesn't wait till signal is processed. I saw this on Windows 7. If you want I can only try to attach Wireshark frames. Kind regards, Radosław Smogura http://www.softperience.eu -- 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] Store base64 in database. Use bytea or text?
Hi,
In means of database, it is impossible. If you want to cache, add
version or last modified column, then ask for changes and cache data
locally.
Kind regards,
Radosław Smogura
http://softperience.eu
On Fri, 28 Jan 2011 13:32:31 +, Andre Lopes wrote:
Hi,
Another question about this subject.
It is possible to cache this images from the database? In the future
I
will need to cache the pictures.
If you have any knowledge about this, please give me a clue.
Best Regards,
On Wed, Jan 26, 2011 at 2:09 PM, Bill Moran
wrote:
In response to Dmitriy Igrishin :
2011/1/26 Andre Lopes
> Thanks for the reply.
>
> I will mainly store files with 100kb to 250kb not bigger than
this.
>
> PQescapeByteaConn is not available in a default installation of
> PostgreSQL? My hosting account hava a standard installation of
> PostgreSQL. There are other options for escaping binary files?
>
> Best Regards,
>
PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP,
'base64'));
where dat column of table img of type bytea.
More specifically:
$query = "INSERT INTO image_data (bytea_field) VALUES ('" .
pg_escape_bytea($binary_data) . "')";
pg_query($query);
And to get it back out:
$query = "SELECT bytea_field FROM image_data";
$rs = pg_query($query);
$row = pg_fetch_assoc($rs);
$binary_data = pg_unescape_bytea($row['bytea_field']);
(note that I may have omitted some steps for clarity)
DO NOT use parametrized queries with PHP and bytea (I hate to say
that,
because parametrized fields are usually a very good idea). PHP has
a
bug that mangles bytea data when pushed through parametrized fields.
PHP bug #35800
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] XML Encoding problem
Hi, I have test database with UTF-8 encoding. I putted there XML ЁĄ¡, (U+0401, U+0104, U+00A1). I changed client encoding to iso8859-2, as the result of select I got ERROR: character 0xd081 of encoding "UTF8" has no equivalent in "LATIN2" Stan SQL:22P05. I should got result with characters entities for unparsable characters &#...;. Kind regards, Radosław Smogura -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Implement timestamp pseudotype
In order to implement sending in binary mode timestamps with / without timezone I want to create pseudo type. Driver should create this autmaticly, if it will have enough permissions. What method should I implement? 1) Of course cast to timestamp, date, time with / without time zone, in, out, send, recv. 2) Any other methods are required or desired, I mean comparison operators, etc? Kind regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] System trigger
Hi, Is any solution (I mean in code and internal based), any API. That allows to create "system" trigger or handle on table. I'm interested in tracking changes and coercing values on row change/insert/remove - user may not to disable such "trigger". In addition It is possible to track changes to cid, for e.g. when VACCUM/CLUSTER will move row? Regards, Radek -- 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] unexpected EOF on client connection vs 9.0.3
On Tue, 08 Mar 2011 11:30:10 +0800, Craig Ringer wrote: On 08/03/11 02:49, Piotr Czekalski wrote: I've checked and verified that all connections are closed within the code, what's more, the problem has appeared just as I've moved server from Fedora Linux x86_64 running Postgres 8.4.2 to the Windows and 9.0.3 (details above) thus I conclude this is not a client problem indeed (the failure didn't occure on Linux). Windows firewall? You can also see these error reports when the connections are closed uncleanly, without a proper backend close message. Perhaps you have client processes crashing? Or doing hard shutdowns where the client code doesn't get a chance to run any cleanup/dtors/etc? -- Craig Ringer I think in 8.x releases is no need to make any spacial close operation, or at least You do not get notice, about it. Closing socket is enough. E.g. JDBC driver closes connection, by closing socket. In 9 this changed and I see many of such notices in log. Probably Your driver, as many others, is written to close socket without backanad message. Regards, Radek -- 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] Using bytea field...
On Wed, 09 Mar 2011 13:27:16 +0200, Sim Zacks wrote: MD5 is not collision resistant (using the immortal words of wikipedia http://en.wikipedia.org/wiki/MD5). This means that it is possible that multiple images will return the same md5 hash. The question is, if it screws up and says that an image already exists and then returns a different image when querying for it, how bad would that be. I've seen a lot of discussions in the past few years about how problematic the md5 approach is. Sim On 03/08/2011 09:06 PM, Andy Colson wrote: On 3/8/2011 12:28 PM, Andre Lopes wrote: Hi, I'm using a bytea field to store small images in base64. I need to know if I can compare bytea rows for equality. To know for example if the file already exists in the database, this is possible with bytea? Best Regads, You dont need to use both base64 and bytea. You can store base64 in text field... or just store the photo as-is into bytea. To answer your question: it would be faster if you computed an md5 (or sha or whatever) and stored it in the db, then you could check to see if an image exists by searching for the md5, which would be way faster, an send a lot less data over the wire. -Andy Every hash algorithm is not collision resistant, and in theory You need to check equality of images. Hash function says that if hashes are different, then images are different. It not says if hashes are same then images are same. Regards, Radek -- 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] Understanding Datum
On Thu, 24 Mar 2011 10:50:32 +0530, Nick Raj wrote:
If Datum contains only the value (not having type specific info.),
then
Suppose i want to print the Datum V value (already defined in
postgres)
then printf("%??", V);
Because V is assigned by PG_GETARG_POINTER(1);
I dont having the information of type Datum.
How to print the value of Datum in postgres?
On Thu, Mar 24, 2011 at 2:35 AM, Tom Lane wrote:
Nick Raj writes:
> In postgres, typedef uintptr_t Datum
> Datum is getting value from PG_GETARG_POINTER(1);
> But, now problem is how would i know the type of
PG_GETARG_POINTER(1)
> (postgres internally pass this argument) to figure out datum
type?
Datum does not carry any type information, only a value.
Functions are
typically coded to know their input types a priori. If you want
to
write code that is not type-specific then youd better be passing
around
type OIDs as well as values.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
[2])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general [3]
Links:
--
[1] mailto:nickrajj...@gmail.com
[2] mailto:pgsql-general@postgresql.org
[3] http://www.postgresql.org/mailpref/pgsql-general
[4] mailto:t...@sss.pgh.pa.us
You may only find this from code context. Bear in mind, in simple
words, datum is like void* with additional size header.
If You write C function you now what kind of datum will income, and
what should outcome (You declare this!). If you examine table, it's same
situation, but You need ask system for this. Even if PG will have type
bytes in Datum, PG supports custom types...
If you wan't to operate on datums You can't work in a way take some
datum and work on it.
You need to obtain /context/, by getting what type tables has on column
x, what type of results function will return, etc; You need to know from
where Your datum income.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Arrays of arrays
Hello, May I ask if PostgreSQL supports arrays of arrays directly or indirectly, or if such support is planned? I'm interested about pseudo constructs like: 1. Directly - (integer[4])[5] - this is equivalent to multidimensional array, but may be differently represented on protocol serialization (as array with array of elements). 2. Indirectly - like create domain d as integer[6], d[11], or by any other means. Currently, I think both of this, are unsupported and array should be only base type. I ask about this, to create extend array support in JDBC. Regards, Radek. -- 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] PostgreSQL trap, and assertion failed
On Thu, 14 Apr 2011 16:57:01 +0800, Craig Ringer wrote:
On 14/04/2011 2:06 PM, Radosław Smogura wrote:
Hello,
I have small crash reporting code, which I use during mmap-ing
database. After
last merge with master I got
TRAP: FailedAssertion("!(slot> 0&& slot<=
PMSignalState->num_child_flags)",
File: "pmsignal.c", Line: 227)
LOG: server process (PID 5128) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
TRAP: FailedAssertion("!(slot> 0&& slot<=
PMSignalState->num_child_flags)",
File: "pmsignal.c", Line: 189)
I've installed crash reports on sigbus and sigseg signals. May I ask
what may
be wrong, and how to prevent this?
The fact that you mention merging with master, and the fact that
you're trapping signals, suggests that you're running a modified Pg.
Without knowing what your changes are, it's probably going to be hard
for anyone to help. Thanks to the joy of C, it's easy to stamp all
over the stack or the heap, so all sorts of weird and wonderful
things
can happen in code unrelated to the real source of the problem...
Yes, indeed, I deal with mmapings and I little play with memory, so
there is some probability that I little "changed" memory. I asked,
because someone may bear in mind such change about this assert. At this
time, this trap do not, seriously, affect my work. Actually I want to
show changes, but those are too big 140kb of bz2, and I have small
problems with vacuum.
Regards,
Radek
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Application user name attribute on connection pool
Hello, I'm not member of this list, but because the 9th version of PostgreSQL is incoming, I would like to ask if there is possibility to add session property of application user - this property, in contrast to login name, should be setted by driver (e.g. JDBC) to current user logged into application server. This for example, will allow triggers to store real user name in history tables or log subsystem (in future) to write who deleted this or that. Similarly solution is in Oracle. I would like to ask, about your opinion about numeric type. I implemented binary read for numeric type in JDBC and I saw, that numeric type is stored inside database as array of shorts no greater then nbase (currently 1). In my opinion this isn't high performance method for two reasons: 1. Arithmetic operations could take more time. 2. It's generally about JDBC and other drivers, transmitting numeric value is complicated and leaks performance for client side, as for long numbers many multiplications and additions must occur. I think about writing something like numeric2 which internally will be represented as the array of ints without nbase. In this context I would like to ask about your opinion 1. If this behaviour can be useful? I imagine performance increase on storing and retrieving values, similarly arithmetic should be faster. (currently 10001 + 10001 requires 4 operations: 2 additions of 1, and 2 additions of 1 from 1*1 and carry move operations, if this value will be stored without nbase, with full bits then addition even in short will take 1 operation 10001+10001 + carry move). 2. Will this decrease other performances? I think that text processing will be much slower, but will this decrease engine performance, as the text conversion is required when creating type? Kind regards, Radek. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Numeric2 - help with library
Hi, I have a problem with running attached type numeric2 on PostgreSQL (8.4). The file is compiled and loaded properly into database. I can create table with this column, and store values there, but when I try to read those values I got segmentation fault. The PostgreSQL doesn't even call method numeric2_out. Method numeric2_in is called properly and result value is as I expect, but when I set type storage to plain I didn't saw in table files (different table space) values that can be treated as proper representation of numeric2. I think I make a mistake in pointers or something, but I can't find what is wrong. Can you see at attached sources and says where the problem is. The code worked in console application with mallocs, etc. By the way, from those sources you can see how I see representation of numerics without NBASE restriction in a way to store number on all available bits. Kind regards, Radosław Smogura numeric2.tar.gz Description: application/gzip -- 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] Table design - postgresql solution
> Hi, > > I have a bit of a DB design question, associated with postgresql in > particular, hopefully thinking it could solve my dilemma. > > This is my setup of 3 tables: > > Table_1 > id_t1 > name > date_of_discovery > history > > Table_2 > id_t2 > name > type > size > > Table_3 > id_t3 > name > location > color > > I want a solution (table or groups of tables) where i can establish > relations between items of every table, for example: > > row with id=2 from Table_1 is connected to row with id=23 from Table_3 > id=9(from Table_2) is connected to id=83(from Table_1) The common in many to many relations is to create for each two tables "joining" table tab1_tab2 with corresponding foreign keys to tab_1 and tab_2. If you want make general relation table, to keep all relations try with CHECK CONSTRAINT with own function inside. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
