[GENERAL] current transaction is aborted, commands ignored until end of transaction block
Hi: After upgrading 7.4.2 to 7.4.5 quite smoothly in a Red Hat 8.0 box, we are having intermitent issues with certain online PHP transactions, returning this error: Warning: pg_exec() query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block Half the times we run the query we get the error, the other half it works. Any ideas? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] current transaction is aborted, commands ignored until end of transaction block
On Tue, Oct 05, 2004 at 07:54:23AM +0200, ruben wrote: After upgrading 7.4.2 to 7.4.5 quite smoothly in a Red Hat 8.0 box, we are having intermitent issues with certain online PHP transactions, returning this error: Warning: pg_exec() query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block Some previous query in this transaction has failed. Are there other error messages before this one? What queries preceded the one that gives this error? Half the times we run the query we get the error, the other half it works. By half do you mean exactly 50% of the time, or are you approximating? Can you identify anything common to the successful transactions that's different from the failed transactions? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] guaranteeing that a sequence never skips
Sometimes a business requirement is that a serial sequence never skips, e.g. when generating invoice/ticket/formal letter numbers. Would an INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice, or must I install a trigger too to do additional checking? If id is defined unique it should be ok but if two of those statments happen to run concurrently you could get duplicate key violations and be prepared to retry the transaction. Savepoints in 8.0 will probably come on handy. Of course concurrency is determined by the length of time you insert a new value and commit. So it would help if you had all other values/statements ready/done by the time you insert into this table, and can do commit straight after this statement. Andre ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] current transaction is aborted, commands ignored until
Hi Michael: Michael Fuhr wrote: After upgrading 7.4.2 to 7.4.5 quite smoothly in a Red Hat 8.0 box, we are having intermitent issues with certain online PHP transactions, returning this error: Warning: pg_exec() query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block Some previous query in this transaction has failed. Are there other error messages before this one? What queries preceded the one that gives this error? Yes, there is some incompatibility between 7.4.2 and 7.4.5 with INT type comparisons, not allowing (7.4.5) something like WHERE int_field = '', so the same transaction failed before the error reported with this one: Warning: pg_exec() query failed: ERROR: invalid input syntax for integer: Half the times we run the query we get the error, the other half it works. By half do you mean exactly 50% of the time, or are you approximating? Can you identify anything common to the successful transactions that's different from the failed transactions? I was approximating. The transaction is exactly the same, I just click on UPDATE button on the web browser and sometimes it worked and sometimes not. Thanks a lot. Ruben. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] table localisation
Hi, I'm trying to create a database with 2 tableSpaces one for data table and one other for indexes I have created a schema on my tablespace for datas and a table in this schema, I would like to verify that this table is on the right tablespace. I don't know how to do that. \d tableName, \d+ tableName, \db+ don't show this information Thanks Frédéric Germaneau ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] more than one instance of pgpool for a backend?
Hi, pgpool seems to be very nice. I will use it in production environment as soon as possible, but have a question regarding pgpool: I have four different databases/user combinations which should have different numbers of possible connection. let my db have 80 concurrent connections and i want to divide them like this: [EMAIL PROTECTED] 10 [EMAIL PROTECTED] 40 [EMAIL PROTECTED] 5 [EMAIL PROTECTED] 25 At the moment i run four different instances af apache with PHP and connect via pg_pconnect. My MaxClients directives are set to the values above, so if all connections are busy you can't even connect to apache and maybe you get a timeout. That's not nice, but i keep my database from overloading and still have enough resources for different databse/user combination. (OT: I would love to here someone running successful PerChildMPM on apache2, at the moment i need four apaches on four different ports to configure MaxClient) How can i achive it with pgpool? Is it possible to run four pgpools for an backend. running pgpool on port 9000-9004 each configured to the values above and have one database cluster handle the pgpool connections? Can i still use synchronous replication and so on. My first guess is: it should work as pgpool handles all connections via independent preforked childs and it should not matter wheather a child is forked from one parent or another. But as i dont know alle the internals, i would like to here an expert opinion. kind regards, janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
On Mon, 4 Oct 2004, Tom Lane wrote: Jimmie H. Apsey [EMAIL PROTECTED] writes: I'd recommend an upgrade to 7.4.5 at your earliest convenience. I have kept up-to-date our Red Hat kernels as you can probably see from the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own version of Postgres alongside and compiled into Red Hat's latest and greatest kernel? If that's true, WHEW! Unfortunately I don't get to dictate Red Hat's backwards-compatibility policies :-( ... and their policy for AS 2.1 is that it's gonna be Postgres 7.1 till it dies. This means that anything that's fundamentally unfixable without an initdb is going to remain broken. AFAIK, the policy is to keep _compatible_ version, which is a sound policy. RH users sould be able to perform upgrades w/o fear of losing anything. I can't speak for the postgresql RPM, but I know their policy is to backport fixes (if possible). Unluckily, sometimes a pg_dumpall restore just won't do. You need to manually edit your dump for the next version of postgres to be able grok it. Nothing hard, usually, just silly stuff, but anyway that rules out an automatic dumprestore at rpm -U time. Of course, no one prevents you from compiling your own version of postgres and running it on a separate dataspace. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Random not so random
In article [EMAIL PROTECTED], Bruno Wolff III [EMAIL PROTECTED] writes: On Mon, Oct 04, 2004 at 10:14:19 -0400, Tom Lane [EMAIL PROTECTED] wrote: It occurs to me that you might be seeing predictability as an indirect result of something else you are doing that somehow tends to synchronize the backend start times. Are you connecting from a cron script that would tend to be launched at the same relative instant within a second? It might improve matters to make the code do something like srandom((unsigned int) (now.tv_sec ^ now.tv_usec)); Using /dev/urandom, where available, might be another option. However, some people may not want their entropy pool getting 4 bytes used up on every connection start up. I think we don't need the randomness provided by /dev/[u]random. How about XORing in getpid? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Function array_to_string(text[], text) does not exist ???
On Tue, Oct 05, 2004 at 12:07:11PM +0200, Johann Robette wrote: I'm trying to call the array_to_string function like this : SELECT array_to_string(array[1, 2, 3], '~^~') -- it comes directly from the doc. I get this error msg : ERROR: parser: parse error at or near [ at character 24 What version of PostgreSQL are you running? According to the Release Notes, array constructors and array_to_string() were added in 7.4. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
RE : [GENERAL] Function array_to_string(text[], text) does not exist ???
Ok, that must be it. In fact I was originally using 7.3 but I thought that we've upgraded to 7.4 but apparently note. What I would do is upgrade it and it should work... Thanls -Message d'origine- De : Michael Fuhr [mailto:[EMAIL PROTECTED] Envoyé : mardi 5 octobre 2004 15:06 À : Johann Robette Cc : [EMAIL PROTECTED] Objet : Re: [GENERAL] Function array_to_string(text[], text) does not exist ??? On Tue, Oct 05, 2004 at 12:07:11PM +0200, Johann Robette wrote: I'm trying to call the array_to_string function like this : SELECT array_to_string(array[1, 2, 3], '~^~') -- it comes directly from the doc. I get this error msg : ERROR: parser: parse error at or near [ at character 24 What version of PostgreSQL are you running? According to the Release Notes, array constructors and array_to_string() were added in 7.4. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Random not so random
On Tue, Oct 05, 2004 at 02:39:13PM +0200, Harald Fuchs wrote: I think we don't need the randomness provided by /dev/[u]random. How about XORing in getpid? What about making the seeding mechanism and perhaps random()'s behavior configurable? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] table localisation
[EMAIL PROTECTED] writes: I have created a schema on my tablespace for datas and a table in this schema, I would like to verify that this table is on the right tablespace. I don't know how to do that. \d tableName, \d+ tableName, \db+ don't show this information Maybe you're using an old copy of psql? I'd expect \d table to show that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Mailing
Hello- I am in the process of translating a site using mysql as the backend over to postgres. I have a lot of time data that I would like to display to the user in the form of a schedule. I am using the to_char function to make the times human friendly to_char(class_schedule.endtime, 'HH:MI:SS AM') which returns 06:30:00 AM - 07:30:00 AM I am really looking to get it outputting like this. 6:30 AM - 7:30 AM I have looked through the documentation and haven't found anything to do this in postgres. I am going to have to do this formating in the application layer? Thanks Todd Marek If you think you understand something it's habit. --Gary Kraftsow--
Re: [GENERAL] Random not so random
Harald Fuchs [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: It might improve matters to make the code do something like srandom((unsigned int) (now.tv_sec ^ now.tv_usec)); I think we don't need the randomness provided by /dev/[u]random. How about XORing in getpid? That sounds like a fine compromise --- it'll ensure a reasonable-size set of possible seeds, it's at least marginally less predictable than now.tv_sec, and it's perfectly portable. No one in their right mind expects random(3) to be cryptographically secure anyway, so doing more doesn't seem warranted. The various proposals to create a more-secure, less-portable variant of random() don't seem appropriate to me for beta. But I'd not object to someone whipping up a contrib module for 8.1 or beyond. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Mailing
* Todd P Marek ([EMAIL PROTECTED]) wrote: I have looked through the documentation and haven't found anything to do this in postgres. I am going to have to do this formating in the application layer? If nothing else I'd think you could create your own function in Postgres to display the time however you like it.. Someone may have a better suggestion, but I wouldn't expect that to be very difficult. Stephen signature.asc Description: Digital signature
Re: [GENERAL] Mailing
I would thought it would be an obvious try: cnagy= select to_char(now(), 'HH:MM AM'); to_char -- 04:10 PM (1 row) HTH, Csaba. On Tue, 2004-10-05 at 16:32, Todd P Marek wrote: Hello- I am in the process of translating a site using mysql as the backendover to postgres. I have a lot of time data that I would like todisplay to the user in the form of a schedule. I am using the to_char function to make the times human friendly to_char(class_schedule.endtime, 'HH:MI:SS AM') which returns 06:30:00 AM - 07:30:00 AM I am really looking to get it outputting like this. 6:30 AM - 7:30 AM I have looked through the documentation andhaven't found anything to do this in postgres. I am going to have todo this formating in the application layer? Thanks Todd Marek If you think you understand somethingit's habit. --Gary Kraftsow-- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Mailing
Todd P Marek [EMAIL PROTECTED] writes: I am using the to_char function to make the times human friendly to_char(class_schedule.endtime, 'HH:MI:SS AM') which returns 06:30:00 AM - 07:30:00 AM I am really looking to get it outputting like this. 6:30 AM - 7:30 AM I have looked through the documentation and haven't found anything to do this in postgres. I think you want 'FMHH:MI AM' ... if not, you'd better be more specific about what you want. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Mailing
On Tue, 5 Oct 2004, Todd P Marek wrote: Hello- I am in the process of translating a site using mysql as the backend over to postgres. I have a lot of time data that I would like to display to the user in the form of a schedule. I am using the to_char function to make the times human friendly to_char(class_schedule.endtime, 'HH:MI:SS AM') which returns 06:30:00 AM - 07:30:00 AM I am really looking to get it outputting like this. 6:30 AM - 7:30 AM For the seconds, do you want seconds if it's not 00, or do you just not want seconds at all? Because removing :SS will get rid of the seconds display. For the leading 0s, you'd probably need to do a user defined function to trim them off, but it'd probably be relatively simple use of ltrim, so you might do something like: create function format_time(time) returns text as ' select ltrim(to_char($1, ''HH:MI AM''), ''0'')' language 'sql'; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Mailing
On Tue, 2004-10-05 at 09:32 -0500, Todd P Marek wrote: __ Hello- I am in the process of translating a site using mysql as the backend over to postgres. I have a lot of time data that I would like to display to the user in the form of a schedule. I am using the to_char function to make the times human friendly to_char(class_schedule.endtime, 'HH:MI:SS AM') which returns 06:30:00 AM - 07:30:00 AM I am really looking to get it outputting like this. 6:30 AM - 7:30 AM Yeah, you're getting exactly what you're asking PostgreSQL to give you. Drop the ':SS' if you don't want the seconds. You can find out more about how you can format your date/times here: http://www.postgresql.org/docs/current/static/functions- formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE Good luck, Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Mailing
SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Mailing
On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote: SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. I wasn't even paying attention to the seconds. I was in fact talking about the leading 0. Thanks to everyone and apologies for my oversight of the seconds clause. Todd Marek ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Mailing
Hey, I didn't know trim is so flexible... cool ! Cheers, Csaba. On Tue, 2004-10-05 at 17:00, Kevin Barnard wrote: SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Mailing
Going back to the documents I think Tom's answer of prepending FM is better then mine.Look at table 9-22 for other options On Tue, 5 Oct 2004 10:06:51 -0500, Todd P Marek [EMAIL PROTECTED] wrote: On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote: SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. I wasn't even paying attention to the seconds. I was in fact talking about the leading 0. Thanks to everyone and apologies for my oversight of the seconds clause. Todd Marek ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] postgresql 8.0 and tsearch2 install under cygwin
Hello, I've just made successful installation of PostgreSQL 8.0 on Cygwin but I have some problems with installing tsearch2 contrib module. This is what make produce: dict_ispell.o(.text+0x1ec):dict_ispell.c: undefined reference to `_pg_strcasecmp ' dict_ispell.o(.text+0x31e):dict_ispell.c: undefined reference to `_pg_strcasecmp ' dict_ispell.o(.text+0x425):dict_ispell.c: undefined reference to `_pg_strcasecmp ' wparser_def.o(.text+0xd1f):wparser_def.c: undefined reference to `_pg_strcasecmp ' wparser_def.o(.text+0xd7e):wparser_def.c: undefined reference to `_pg_strcasecmp ' wparser_def.o(.text+0xdb7):wparser_def.c: more undefined references to `_pg_strc asecmp' follow ispell/SUBSYS.o(.text+0x9d3):spell.c: undefined reference to `_pg_strncasecmp' ispell/SUBSYS.o(.text+0xa3a):spell.c: undefined reference to `_pg_strncasecmp' ispell/SUBSYS.o(.text+0xa6a):spell.c: undefined reference to `_pg_strncasecmp' ispell/SUBSYS.o(.text+0xa91):spell.c: undefined reference to `_pg_strncasecmp' collect2: ld returned 1 exit status dllwrap: gcc exited with status 1 make: *** [libtsearch2.a] Error 1 Any solutions ? ML ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Set locale per query?
Hi, Is there a way to set locale per query (i use latest JDBC drivers). I am in situation, where we need to make ORDER BY querys over data that is in various langages - English, Russian, Estonian, Latvian. I know that there are issues with indexing that column in such case, but what are my options other then sort results in my program manally. Rigmor Ukuhe --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.770 / Virus Database: 517 - Release Date: 27.09.2004 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] looking for Mac pgEdit beta testers
Hi, I'm working on a new product for PostgreSQL (description below) and I'm looking for a few more Mac users who might be interested in beta testing. The first release will be for Mac OS X 10.2 or later. A Windows version will follow, most likely in November. Drop me an email if you can spare a little time to take a look and provide some feedback. Thanks! John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL = pgEdit is a high performance SQL editor and development environment designed specifically for PostgreSQL relational databases. It supports Emacs style editing with Macintosh and Windows compatibility modes. SQL scripts can be executed directly from the editor without saving. pgEdit features include: * SQL Syntax coloring specifically designed for PostgreSQL PostgreSQL represents function bodies as single quoted strings which most editors render with a single color. pgEdit promotes better usability and correctness by using SQL syntax coloring within the body of function strings. Keyword coloring is based directly on the source code of PostgreSQL. * SQL source code execution without saving the file or switching applications pgEdit can execute the SQL source code for (1) the entire buffer, (2) the highlighted region, (3) the current SQL containing the cursor, or (4) the current SQL line to the end of the file buffer. * Direct integration with psql, the PostgreSQL interactive terminal In addition to SQL, almost all psql commands are supported. This provides a powerful facility for examining, designing, and managing databases. * Extensive and customizable editing facilities pgEdit is based on a Emacs-like text editor with hundreds of commands available. A friendly interface is included to provide online documentation and customize the key bindings for any command. Familiarity with Emacs is not required; pgEdit provides the option emulate traditional Macintosh and Windows editing styles. * Integrated documentation Help in pgEdit is provided with context sensitive links to the online documentation displayed in your preferred web browser. The PostgreSQL documentation is also included with direct links to important sections provided in the pgEditHelp menu. The documentation for the SQL command nearest to the cursor can be retrieved by pressing a user configurable key sequence. * Cross platform and easy to install pgEdit is a native application for both Macintosh and Windows. It does not use Java or require installation of external libraries. You have the option of using the version of psql included with pgEdit or a different version installed anywhere on your hard drive. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] storage representation of DATE and TIME types
please cc me as I am on digetst: What is the storage format of 'date'? Is it like a timestamp? I want to know in order to choose representations in a table that will receive LOTS of reads with a WHERE clause that chooses dates, and TIMES, past a supplied reference. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Postgres 8.0 + JDBC
Hello,
I have an application running
under JBoss.
Up to today, I was using Postgres 7.3 and the appropriate version of the jdbc driver.
In my application, I have to
call a user-defined function which accept in
parameters 2 arrays. Here is the header of my function :
CREATE OR REPLACE FUNCTION getmembers(int8,
int8, _text, _float8)
So I called it using a
prepared statement with setArray() :
double[] weights = {0.5};
String[] names = {foo1, foo2};
java.sql.Array a_names = PostgresArray.create(names);
java.sql.Array a_weights = PostgresArray.create(weights);
ps = conn.prepareStatement(SELECT
* FROM getmembers(?,?,?::_text,?::_float8););
ps.setLong(1,
1);
ps.setLong(2,
2);
ps.setArray(3,
a_names);
ps.setArray(4, a_weights);
ps.executeQuery();
PostgresArray is a
class which I found on the archives.postgresql.org. The code is given is
attached.
All worked fine.
But today, I decided to
upgrade to Postgres 8.0 beta 3.
No problem with the definition
of my function.
I downloaded the appropriate
JDBC driver : pgdev.306.jdbc3.jar.
Now running the same code as
before, I get the error while executing the query :
java.sql.SQLException: ERROR: cannot cast type text to text[]
So, what am I doing wrong?
Is it a beta bug or is my code
incorrect?
What is the correct way to use
SetArray()?
Thanks
JR
package com.postgresql;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Map;
/*
* Array is used collect one column of query result data.
*
* pRead a field of type Array into either a natively-typed
* Java array object or a ResultSet. Accessor methods provide
* the ability to capture array slices.
*
* pOther than the constructor all methods are direct implementations
* of those specified for java.sql.Array. Please refer to the javadoc
* for java.sql.Array for detailed descriptions of the functionality
* and parameters of the methods of this class.
*
* bThis class stolen from postgresql 7.2.1's source tree!!!/b
*
* @see ResultSet#getArray
*
*/
public class PostgresArray implements Array {
private String rawString = null;
private String typeName = null;
private int baseType = -1;
private static final String jdbc2Types[] = {
int2,
int4, oid,
int8,
cash, money,
numeric,
float4,
float8,
bpchar, char, char2, char4, char8, char16,
varchar, text, name, filename,
bytea,
bool,
date,
time,
abstime, timestamp, timestamptz,
_bool, _char, _int2, _int4, _text,
_oid, _varchar, _int8, _float4, _float8,
_abstime, _date, _time, _timestamp, _numeric,
_bytea
};
/*
* This table holds the JDBC type for each entry above.
*
* Note: This must be in the same order as above
*
* Tip: keep these grouped together by the Types. value
*/
private static final int jdbc2Typei[] = {
Types.SMALLINT,
Types.INTEGER, Types.INTEGER,
Types.BIGINT,
Types.DOUBLE, Types.DOUBLE,
Types.NUMERIC,
Types.REAL,
Types.DOUBLE,
Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR,
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
Types.BINARY,
Types.BIT,
Types.DATE,
Types.TIME,
Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY, Types.ARRAY,
Types.ARRAY
};
/*
* Create a new Array
*
* @param conn a database connection
* @param idx 1-based index of the query field to load into this Array
* @param field the Field descriptor for the field to load into this Array
* @param rs the ResultSet from which to get the data for this Array
*/
PostgresArray(String rawString, int baseType, String typeName) throws SQLException
{
this.rawString = rawString;
this.baseType = baseType;
this.typeName = typeName;
if (this.typeName.startsWith(_)) {
this.typeName = this.typeName.substring(1);
}
}
public static Array create(Object[] array) throws SQLException {
throw new SQLException(Not Implemented);
}
public static Array create(int[] array) throws SQLException {
if (array == null)
return new PostgresArray(null, -1, null);
StringBuffer sb = new StringBuffer({);
for (int x = 0; x array.length; x++) {
if (x 0)
sb.append(,);
sb.append(\).append(array[x]).append(\);
}
sb.append(});
return new PostgresArray(sb.toString(), Types.INTEGER, int);
}
public static Array
Re: [GENERAL] Random not so random
A better way would be to seed a Mersenne Twister PRNG at server startup time and then use the same generator for all subsequent calls. http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html The period is exceptionally long, and it has many excellent properties. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Stimits Sent: Monday, October 04, 2004 7:23 AM Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Random not so random Tom Lane wrote: Arnau Rebassa [EMAIL PROTECTED] writes: I'm using a debian linux as OS with a 2.4 kernel running on it. Incidentally, are you reconnecting every time or is it that multiple calls in a single session are returning the same record? I'm reconnecting each time I want to retrieve a message. Hmm. postmaster.c does this during startup of each backend process: gettimeofday(now, tz); srandom((unsigned int) now.tv_usec); If it uses the same seed from the connection, then all randoms within a connect that has not reconnected will use the same seed. Which means the same sequence will be generated each time, which is why it is pseudo-random and not random. For it to be random not just the first call of a new connection, but among all calls of new connection, it would have to seed it based on time at the moment of query and not at the moment of connect. A pseudo-random generator using the same seed will generate the same sequence. D. Stimits, stimits AT comcast DOT net ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Set locale per query?
Rigmor Ukuhe [EMAIL PROTECTED] writes: Is there a way to set locale per query (i use latest JDBC drivers). I am in situation, where we need to make ORDER BY querys over data that is in various langages - English, Russian, Estonian, Latvian. I know that there are issues with indexing that column in such case, but what are my options other then sort results in my program manally. There's no support for that in the standard backend. If you check in the list archives, someone has posted a sorting function that takes locale as one argument --- not sure about efficiency but it might do as a stopgap. I believe it was last posted just a couple weeks ago in -patches or -hackers. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] problem with pg_restore
I cant seem to get pg_restore to work for me. here's how im calling it: pg_restore -d dbname -C -v -U user src tar file It keeps on telling me that the database dbname does not exist so it cant connect to it. I thought the -C flag creates the database, what am i missing here? Thanks Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Verifying Referential Integrity
Title: Message So,as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database?
Re: [GENERAL] Verifying Referential Integrity
On Tue, Oct 05, 2004 at 02:03:09PM -0400, Geisler, Jim wrote: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. ... just like it doesn't have a way of verifying loss of tables or any other object. If someone messes up the schema (be it via ALTER commands or directly modifying system catalogs), Postgres will continue working with the new schema. Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database? Maybe do pg_dump -s periodically and compare to a known good version? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Si no sabes adonde vas, es muy probable que acabes en otra parte. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Verifying Referential Integrity
Geisler, Jim wrote: Message So,as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database? Of course, Tom Lane suggested I look at the pg_trigger table. I suppose I'll have to do this from time to time. His good point on an old database system (7.1.3) like what is included within Red Hat AS 2.1 is as he wrote, "Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). You can sort out which is which by looking at the tgargs field --- note how the referencing and referenced table and field names are embedded in that. I suspect that some of these triggers got dropped or disabled. If you don't find all three triggers for some one constraint, the best bet is to drop any remaining triggers from the set and then issue ALTERTABLE ADD FOREIGN KEY to re-make a consistent trigger set." I did what he suggested and then re-created the offending table and altered the other offending table. Now, my database has working referential integrity between the two tables involved. Jim Apsey
Re: [GENERAL] Random not so random
DS == D Stimits [EMAIL PROTECTED] writes: DS If it uses the same seed from the connection, then all randoms within DS a connect that has not reconnected will use the same seed. Which means DS the same sequence will be generated each time, which is why it is DS pseudo-random and not random. For it to be random not just the first DS call of a new connection, but among all calls of new connection, it DS would have to seed it based on time at the moment of query and not at DS the moment of connect. A pseudo-random generator using the same seed DS will generate the same sequence. You clearly demonstrate you do not understand the purpose of a seed in a PRNG, nor how PRNG's in general work. You want to seed it once and only once per process, not every time you issue a query. And nobody said to use the same seed every time, either. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] problem with pg_restore
Alexander Cohen [EMAIL PROTECTED] writes: pg_restore -d dbname -C -v -U user src tar file It keeps on telling me that the database dbname does not exist so it cant connect to it. I thought the -C flag creates the database, what am i missing here? It does, but you have to connect somewhere first so you can issue the CREATE DATABASE command. I think the common approach is to use -d template1 in combination with -C. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Verifying Referential Integrity
Geisler, Jim [EMAIL PROTECTED] writes: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity. What are you trying to accomplish here, and in what PG version? Are you trying to check that PG thinks that a foreign-key relationship is installed? In recent versions psql's \d will tell you that. If you're dealing with an old version you might have to look directly at the system catalogs. Are you not trusting that an active foreign-key relationship has been correctly enforced? Then I think you want to do some kind of JOIN query to see if you can find any rows with no master row. (You could actually do this by temporarily creating a new, redundant FK constraint; but if you are feeling that paranoid you're likely not going to trust the system's answer anyway...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Random not so random
Vivek Khera wrote: DS == D Stimits [EMAIL PROTECTED] writes: DS If it uses the same seed from the connection, then all randoms within DS a connect that has not reconnected will use the same seed. Which means DS the same sequence will be generated each time, which is why it is DS pseudo-random and not random. For it to be random not just the first DS call of a new connection, but among all calls of new connection, it DS would have to seed it based on time at the moment of query and not at DS the moment of connect. A pseudo-random generator using the same seed DS will generate the same sequence. You clearly demonstrate you do not understand the purpose of a seed in a PRNG, nor how PRNG's in general work. You want to seed it once and only once per process, not every time you issue a query. And nobody said to use the same seed every time, either. Sorry, at the time I don't believe PRNG was part of the conversation, that came after (maybe I missed that). What I saw were functions based on one-way hashes...is that incorrect? For one-way hashes and pseudo-random generators using some form of hash a different seed should be used or else the pattern will be the same when using the same data. From the man page on srandom(): The srandom() function sets its argument as the seed for a new sequence of pseudo-random integers to be returned by random(). These sequences are repeatable by calling srandom() with the same seed value. If no seed value is provided, the random() function is automat- ically seeded with a value of 1. The srandom() caught my eye in the earlier email, not PRNG. You are welcome to re-use srandom() without a new seed if you want. D. Stimits, stimits AT comcast DOT net ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] SSL connection between PHP4 PostgreSQL ???
Hi, I've setup a postgres server (7.4) and confirmed that SSL is enabled - I can successfully connect via tcp socket over SSL using the psql client. From PHP4, how can I get the pg_connect function to negotiate an SSL connection? I gather from researching the issue that pg_connect uses the same libraries as psql, so that this should be possible. But I've tried every syntax I can think of... the options parameter to pg_connect is not well documented. I've played with all varieties of requiressl or ssl, alone or as a boolean, e.g., requiressl=true, etc... If not possible in PHP4, is it in PHP5? Any help greatly appreciated! MikeM Mike Morris The Music Place 1617 Willowhurst Avenue San Jose, CA 95125 (408) 445-ARTS (2787) Your Free Historical Quote: Above all, I know there is a Supreme Being who rules the affairs of men and whose goodness and mercy have always followed the American people, and I know He will not turn from us now if we humbly and reverently seek His powerful aid. - Grover Cleveland, Second Inaugural Address, March 4, 1893 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SSL connection between PHP4 PostgreSQL ???
From PHP4, how can I get the pg_connect function to negotiate an SSL connection? I gather from researching the issue that pg_connect uses the same libraries as psql, so that this should be possible. But I've tried every syntax I can think of... the options parameter to pg_connect is not well documented. I've played with all varieties of requiressl or ssl, alone or as a boolean, e.g., requiressl=true, etc... First, are you sure that SSL is linked into PHP? Use phpinfo() if you're not sure. Second, what are you using for a connect statement? Here's a sanitized version of one that works for me: DB::connect(pgsql://foobar:[EMAIL PROTECTED]/dbnm?requiressl=true); -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SSL connection between PHP4 PostgreSQL ???
Mike Morris wrote: Hi, I've setup a postgres server (7.4) and confirmed that SSL is enabled - I can successfully connect via tcp socket over SSL using the psql client. From PHP4, how can I get the pg_connect function to negotiate an SSL connection? Hello, Have not done this in a while but I believe you have to compile PHP with openssl support. Sincerely, Joshua D. Drake I gather from researching the issue that pg_connect uses the same libraries as psql, so that this should be possible. But I've tried every syntax I can think of... the options parameter to pg_connect is not well documented. I've played with all varieties of requiressl or ssl, alone or as a boolean, e.g., requiressl=true, etc... If not possible in PHP4, is it in PHP5? Any help greatly appreciated! MikeM Mike Morris The Music Place 1617 Willowhurst Avenue San Jose, CA 95125 (408) 445-ARTS (2787) Your Free Historical Quote: Above all, I know there is a Supreme Being who rules the affairs of men and whose goodness and mercy have always followed the American people, and I know He will not turn from us now if we humbly and reverently seek His powerful aid. - Grover Cleveland, Second Inaugural Address, March 4, 1893 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
