[BUGS] Weak Password!
Hi, I had PostgreSQL-beta-5 installed until recently. I uninstalled it & installed the PostgreSQL-8.01 official Postgres release. While installing, I came across a slight problem: In the service configuration gui, I entered a new service account user, as follows: Account name as "postgresnew", Account password as "Post2restes$" Verify password as "Post2restes$" It gave me a weak password error. So I went back & tried to enter a stronger password. It wont even accept a password such as [EMAIL PROTECTED] How do I set a password which it accepts fine? What are the requirements for such a password? Thanks & Regards, Mehul ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Weak Password!
Mehul, In the service configuration gui, I entered a new service account user, as follows: this rather sounds like some distro configuration tool. What kind of distro are you using? If it's a problem wit hthe package you have to ask the vendors/supporters of your distro. Greetings, Klaus ---(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: [BUGS] Weak Password!
Hi, I am using the Native Windows version which I downloaded it from the http://www.postgresql.org/ftp/v8.0.1/ Regards, Mehul -Original Message- From: Klaus Naumann [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 12:32 PM To: Mehul Doshi-A20614 Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Weak Password! Mehul, > In the service configuration gui, I entered a new service account > user, as follows: this rather sounds like some distro configuration tool. What kind of distro are you using? If it's a problem wit hthe package you have to ask the vendors/supporters of your distro. Greetings, Klaus ---(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: [BUGS] Weak Password!
>Hi, > >I am using the Native Windows version which I downloaded it >from the http://www.postgresql.org/ftp/v8.0.1/ The way most Windows systems are set up today, to make for a really stong password you need at least 14 characters long. I beleive the actual recommendation for a strong service account password (one that you will not be logging in as regularly) is now up to 31, but the installer will warn you at 14. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1544: Problems with ODBC
The following bug has been logged online: Bug reference: 1544 Logged by: Pavlo Zhezhnych Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows 2000/XP Description:Problems with ODBC Details: I installed PostgreSQL and ODBC driver on my computer under Windows 2000 and used MS Access as a client program. I created tables with varchar(50) primary key, and accessed them with MS Access creating linking tables. There was no problem, if I inserted latin letters or digits into a primary key of a MS Access linking table. But I'm ukrainian and tried to insert ukrainian letters into the primary key via ODBC. I received a strange message from ODBC that some wrong characters encountered. In pgAdminIII I saw that my records were successfully inserted (even with cyrillic letters in primary key). I suppose that there are some bugs in ODBC driver, especially in cyrillic letters processing in primary keys. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
On Mar 14, 2005, at 12:13 AM, Tom Lane wrote: "John Engelhart" <[EMAIL PROTECTED]> writes: Since I'm developing an SQL based application, I routinely "start from scratch" with a script that deletes all the tables in my database and rebuilds them. A problem started when I upgraded from 7.4.7 to 8.0.1 in that the first run after the clean takes an UNUSUALLY long time to complete, on the order of a few hundred inserts/sec. A normal run takes ~30 seconds. A from scratch run takes ~15 minutes, with the next run completing in 30 seconds with zero changes. The 8.0 planner is intentionally sensitive to the current actual physical sizes of tables. It sounds like you've managed to get it to plan something on the assumption that the tables are tiny and keep using that plan after they aren't tiny any more. The old planner had the same kind of issue but it was far easier to hit, so "revert that change" isn't an answer that I'm particularly interested in. I found another data point yesterday. It seems to be session related. I tried various combinations of "after X number of statements, COMMIT, ANALYZE, CHECKPOINT" and none of them helped. The one that did help is after X number of statements, close the database handle and re-open it. So, my program run does about 60K SQL statements, inserting about 22K records. After 500 statements, I close the handle and re-open it. Completes in 30 seconds. Hopefully this is enough info to track down and recreate the problem Not really. Let me know if there's anything that you need. regards, tom lane ---(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
[BUGS] BUG #1549: initdb doesn't work
The following bug has been logged online: Bug reference: 1549 Logged by: Daniel van Eeden Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Debian GNU/Linux for SPARC (sarge) Description:initdb doesn't work Details: creating template1 database in /opt/postgresql/data/base/1 ... FATAL: semctl(10977333, 16, SETVAL, 536) failed: Invalid argument child process exited with exit code 1 initdb: removing data directory "/opt/postgresql/data" Postgresql is compiled from the official sources in 64-bit mode. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference
Dear Tom and Neil, Thanks very much for your help, and your explanations. This makes a lot of sense, and I agree - this bug is definitely invalid. Best wishes Richard Tom Lane wrote: "Richard Neill" <[EMAIL PROTECTED]> writes: SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE tbl_instruments.priceband=tbl_prices.priceband; ERROR: column reference "priceband" is ambiguous I think that the first query ought to succeed, since although priceband is ambiguous (it could mean either tbl_prices.priceband or tbl_instruments.priceband), the information in the WHERE clause means that they are explicitly equal, and so it doesn't matter which one we use. Doing that would be contrary to the SQL specification, AFAICS. However, you can get the effect you want by writing the query like SELECT instrument,priceband,pounds FROM tbl_instruments JOIN tbl_prices USING (priceband); which both provides the join condition and logically merges the two input columns into just one output column. regards, tom lane ---(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: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP
On Fri, 11 Mar 2005 10:37:13 +1300, Mark Kirkwood <[EMAIL PROTECTED]> wrote: >Firebird 1.5.1 FreeBSD 5.3 >[correct results] Interbase 6.0: SQL> create table tab (col integer); SQL> select 1 from tab having 1=0; SQL> select 1 from tab having 1=1; 0<---:-) SQL> insert into tab values(1); SQL> insert into tab values(2); SQL> select 1 from tab having 1=0; SQL> select 1 from tab having 1=1; 1 SQL> Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1543: Problem with restore DB
The following bug has been logged online: Bug reference: 1543 Logged by: Ales Vojacek Email address: [EMAIL PROTECTED] PostgreSQL version: 8 Operating system: W2000 Description:Problem with restore DB Details: We try to come from MSSQL. We heva no trigger or stored procedures. When I just create tables, then transfer data using copy command to fill tables, then create primary keys, indexes, foreign keys it takes on my hardware cca 4 hours. When I backup database using pg_dump and then I try to restore DB into empty DB. I take 12+ hours and does not end. The problem was that create FK on some columns take a lot of time. Especially on of them takes 10+ hours it was on tables (2 000 000 and 10 000 rows). All database is about 18 GB and I have 1 GB RAM. When I try to setup maintenance_work_mem from 30 to 50 there was not solution only if I have 30 there was more much I/O then 50. If I have set 50 there few I/O and CPU 100%. But the tim of creafion FK seemd to be same. The time of creation FK was much sorter when I reindex tables which are affected by creation of FK. After that with maintenance_work_mem set to 40 it ends afte few minutes. If I was talking about my problem on IRC they say to report it to you. I hope that you can explain it to me or you can fix it. If we try to backup and restore same database on our linux box it was done in time which we hope cca two hours. It is on faster hw, but I thing that restore from dump of DB cannot have a problem with creation of FK. Thanks a excuse me for my poor english. Ales ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1547: CREATE TYPE AS error
The following bug has been logged online: Bug reference: 1547 Logged by: John Smith Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows 2000 Description:CREATE TYPE AS error Details: When trying to create a complex type, the parser produces an error. e.g. CREAYE TYPE product AS (name varchar, price numeric); produces ERROR: parser: parse error at or near "as" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1551: You need an import/export feature
The following bug has been logged online: Bug reference: 1551 Logged by: Bobby Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: WinXP Pro Description:You need an import/export feature Details: You guys badly need an import/export feature. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1548: The installation fails
The following bug has been logged online: Bug reference: 1548 Logged by: Javier Solís Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Windows XP Description:The installation fails Details: In the installation process, at "Installing..." "Initializing databuse cluster (this may take a minute or two)..." I have the following message in a window apart: "Failed to create process for initdb: "(follows in Spanish as I have the Spanish version of Windows)" Error de inicio de sesión: no se ha concedido al usuario este tipo de inicio de sesión en este equipo." That paragraph means (more ore less): Error at the begining of the session: the user hasn't got this type of session init in this computer. Thank you Best regards ---(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
[BUGS] BUG #1545: LIBPQ Windows Version not calling WSACleanup for every WSAStartup
The following bug has been logged online: Bug reference: 1545 Logged by: Jason Erickson Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1,7.4.7 Operating system: Windows Description:LIBPQ Windows Version not calling WSACleanup for every WSAStartup Details: Taken from microsoft at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/win sock/wsastartup_2.asp --- An application must call one WSACleanup call for every successful WSAStartup call to allow third-party DLLs to make use of a WS2_32.DLL on behalf of an application. This means, for example, that if an application calls WSAStartup three times, it must call WSACleanup three times. The first two calls to WSACleanup do nothing except decrement an internal counter; the final WSACleanup call for the task does all necessary resource deallocation for the task. -- The only place WSACleanup is being called is libpqdll when the process detaches the DLL (if the libpq is not staticly linked in), which matches up with the WSAStartup when the process attaches to the DLL. The WSAStartup in the fe-connect.c->makeEmptyPGconn() does not have a matching WSACleanup. WSACleanup could possibly be placed in freePGconn(), but unsure if all possible error cases will go through this function. This problem exists in both 8.0.1 and 7.4.7 of the libpq interface for Windows. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1546: Temp table isn't deleted at the end of a transaction / ON COMMIT DROP has no effect
The following bug has been logged online: Bug reference: 1546 Logged by: Oliver Siegmar Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.7 / 8.0.1 Operating system: Linux x86 Description:Temp table isn't deleted at the end of a transaction / ON COMMIT DROP has no effect Details: Hello, I've probably found a temp table bug in PostgreSQL (tested with 7.4.7 and 8.0.1 on Linux x86). Here's a demonstration of the bug: CREATE FUNCTION testfunction() RETURNS void AS ' BEGIN CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP; INSERT INTO testtable (field) VALUES (1); -- DROP TABLE testtable; RETURN; END; ' LANGUAGE 'plpgsql'; database=# SELECT testfunction(); testfunction -- (1 row) database=# SELECT testfunction(); ERROR: relation with OID 29308882 does not exist CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)" PL/pgSQL function "testfunction" line 4 at SQL statement No transaction has been started manually. If I drop the temporary testtable manually within the PL/pgSQL function, everything runs fine. Bug or feature? ;-) Cheers, Oliver ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] random FlushRelationBuffers() failed on Pg8.0.1 Win32
PG version =
"PostgreSQL 8.0.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3
(mingw special 20030504-1)"
Attached is the regression result of my test on Pg8.0.1 win32. The problem
is quite random. On the second round, it just disappeared.
first round --
*** ./expected/strings.out Sun Mar 14 12:25:18 2004
--- ./results/strings.out Mon Mar 14 09:51:59 2005
***
*** 640,658
--- 640,668
(4 rows)
DROP TABLE toasttest;
+ ERROR: FlushRelationBuffers("pg_toast_18740", 0): block 1 is referenced
(private 0, global 1)
--
-- test substr with toasted bytea values
--
CREATE TABLE toasttest(f1 bytea);
+ ERROR: relation "toasttest" already exists
insert into toasttest values(decode(repeat('1234567890',1),'escape'));
+ ERROR: column "f1" is of type text but expression is of type bytea
+ HINT: You will need to rewrite or cast the expression.
insert into toasttest values(decode(repeat('1234567890',1),'escape'));
+ ERROR: column "f1" is of type text but expression is of type bytea
+ HINT: You will need to rewrite or cast the expression.
--
-- Ensure that some values are uncompressed, to test the faster substring
-- operation used in that case
--
alter table toasttest alter column f1 set storage external;
insert into toasttest values(decode(repeat('1234567890',1),'escape'));
+ ERROR: column "f1" is of type text but expression is of type bytea
+ HINT: You will need to rewrite or cast the expression.
insert into toasttest values(decode(repeat('1234567890',1),'escape'));
+ ERROR: column "f1" is of type text but expression is of type bytea
+ HINT: You will need to rewrite or cast the expression.
-- If the starting position is zero or less, then return from the start of
the string
-- adjusting the length to be consistent with the "negative start" per
SQL92.
SELECT substr(f1, -1, 5) from toasttest;
***
*** 690,695
--- 700,706
(4 rows)
DROP TABLE toasttest;
+ ERROR: FlushRelationBuffers("pg_toast_18740", 0): block 1 is referenced
(private 0, global 1)
--
-- test length
--
==
*** ./expected/type_sanity.out Thu Aug 5 05:34:34 2004
--- ./results/type_sanity.out Mon Mar 14 09:52:22 2005
***
*** 258,262
(p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
attrelid | attname | oid | typname
--+-+-+-
! (0 rows)
--- 258,263
(p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
attrelid | attname | oid | typname
--+-+-+-
! 18740 | f1 | 25 | text
! (1 row)
==
*** ./expected/misc.out Fri Mar 4 16:06:11 2005
--- ./results/misc.out Mon Mar 14 09:54:07 2005
***
*** 658,667
timestamptz_tbl
timetz_tbl
tinterval_tbl
toyemp
varchar_tbl
xacttest
! (97 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
--- 658,668
timestamptz_tbl
timetz_tbl
tinterval_tbl
+ toasttest
toyemp
varchar_tbl
xacttest
! (98 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
==
*** ./expected/portals_p2.out Mon Jul 16 13:07:00 2001
--- ./results/portals_p2.out Mon Mar 14 09:54:10 2005
***
*** 4,122
BEGIN;
DECLARE foo13 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 50;
DECLARE foo14 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 51;
DECLARE foo15 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 52;
DECLARE foo16 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 53;
DECLARE foo17 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 54;
DECLARE foo18 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 55;
DECLARE foo19 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 56;
DECLARE foo20 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 57;
DECLARE foo21 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 58;
DECLARE foo22 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 59;
DECLARE foo23 CURSOR FOR
SELECT * FROM onek WHERE unique1 = 60;
DECLARE foo24 CURSOR FOR
SELECT * FROM onek2 WHERE unique1 = 50;
DECLARE foo25 CURSOR FOR
SELECT * FROM onek2 WHERE unique1 = 60;
FETCH all in foo13;
! unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 |
string4
! -+-+-+--+-++-+--+-
+---+--+-+--+--+--+-
! 50 | 253 | 0 |2 | 0 | 10 | 0 | 50 |
50 |50 | 50 | 0 |1 | YB | TJ | xx
! (1 row)
!
FETCH all in foo14;
! unique1
[BUGS] BUG #1550: LOCK TABLE in plpgsql function doesn't work.
The following bug has been logged online:
Bug reference: 1550
Logged by: Spencer Riddering
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6
Operating system: Debian Woody ( Postgresql from backports.org)
Description:LOCK TABLE in plpgsql function doesn't work.
Details:
When a LOCK TABLE statement is included in a plpgsql function it does not
actually lock the table.
But, if prior to calling the function I execute a seperate statement using
the same connection and same transaction then the LOCK TABLE does work.
I think the expectation is that LOCK TABLE should actually lock the table
even when included in a plpgsql function.
I used JDBC (pg74.215.jdbc3.jar) to discover this behavior.
/* FC_PROCESS_ORDER /
DECLARE
in_receipt ALIAS FOR $1;
in_familyName ALIAS FOR $2;
in_givenNameALIAS FOR $3;
in_address1 ALIAS FOR $4;
in_address2 ALIAS FOR $5;
in_zipCode ALIAS FOR $6;
in_area ALIAS FOR $7;
in_areaDetail ALIAS FOR $8;
in_emailAddress ALIAS FOR $9;
in_product ALIAS FOR $10;
in_phoneALIAS FOR $11;
in_country ALIAS FOR $12;
p_curtime timestamp;
p_payment_record RECORD;
p_payment_consumed RECORD;
p_updated_oid oid; -- set to NULL
p_order_id int4; -- set to NULL
p_customer_id int4; -- set to NULL
p_tmp_order_record RECORD;
BEGIN
-- LOCK TABLE orders IN SHARE ROW EXCLUSIVE MODE;
-- LOCK TABLE payments IN SHARE ROW EXCLUSIVE MODE;
p_curtime := 'now';
-- Determine wether payment has occured.
SELECT INTO p_payment_record * from payments where in_receipt =
payments.receipt;
IF NOT FOUND THEN
RETURN -101; -- PAYMENT_NOT_FOUND
END IF;
-- *** Payment was recieved ***
-- Make sure that the payment is not used.
-- SELECT INTO p_tmp_order_record * FROM orders WHERE payment_id =
p_payment_record.id;
SELECT INTO p_tmp_order_record * FROM orders WHERE payment_id =
p_payment_record.id;
IF FOUND THEN
RETURN -102; -- PAYMENT_CONSUMED
END IF;
-- *** Payment is available ***
-- Add user data.
INSERT INTO customers (family_name, given_name, address_1,
address_2, zip_code, area,area_detail, email , phone ,
country)
VALUES(in_familyName, in_givenName, in_address1,
in_address2, in_zipCode, in_area, in_areaDetail, in_emailAddress, in_phone,
in_country);
-- Find the newly created id.
GET DIAGNOSTICS p_updated_oid = RESULT_OID;
SELECT INTO p_customer_id id from customers where OID = p_updated_oid;
-- *** customers record added *** ---
-- *** Add orders Record *** ---
INSERT INTO orders (customer_id, payment_id ,product_id)
VALUES (p_customer_id, p_payment_record.id, in_product);
-- *** orders record added *** ---
GET DIAGNOSTICS p_updated_oid = RESULT_OID;
SELECT INTO p_order_id id from orders where OID = p_updated_oid;
RETURN p_order_id;
END;
/***/
/***/
/***/
/***/
/***/
/***/
/***/
/***/
/** Java Code **/
// Get Result code/transaction id.
int int_transactId;
Connection conn = null;
try {
conn = ds.getConnection();
conn.setAutoCommit(false);
// This is good. We see updates after they are commited.
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
// Call out to database
CallableStatement callstat = null;
Statement stat = null;
ResultSet rs = null;
try {
// I had to add these lines to actually
// Lock the tables.
stat = conn.createStatement();
stat.executeUpdate("LOCK TABLE orders IN SHARE ROW EXCLUSIVE
MODE");
stat.close();
stat = conn.createStatement();
stat.executeUpdate("LOCK TABLE payments IN SHARE ROW
EXCLUSIVE MODE");
stat.close();
// 1 2 3 4 5 6 7 8 9 10 11 12 13
callstat = conn
.prepareCall("{ ? = call FC_PROCESS_ORDER(?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?) }");
callstat.registerOutParameter(1, java.sql.Types.INTEGER);
callstat.setString(2, receipt);
callstat.setString(3, familyName);
callstat.setString(4, givenName);
callstat.setString(5, address1);
callstat.setS
[BUGS] Possible temp table bug in PostgreSQL 7.4.7 / 8.0.1
Hello, I've probably found a temp table bug in PostgreSQL (tested with 7.4.7 and 8.0.1 on Linux x86). Here's a demonstration of the bug: CREATE FUNCTION testfunction() RETURNS void AS ' BEGIN CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP; INSERT INTO testtable (field) VALUES (1); -- DROP TABLE testtable; RETURN; END; ' LANGUAGE 'plpgsql'; database=# SELECT testfunction(); testfunction -- (1 row) database=# SELECT testfunction(); ERROR: relation with OID 29308882 does not exist CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)" PL/pgSQL function "testfunction" line 4 at SQL statement No transaction has been started manually. If I drop the temporary testtable manually within the PL/pgSQL function, everything runs fine. Bug or feature? ;-) Cheers, Oliver ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1547: CREATE TYPE AS error
On Tue, Mar 15, 2005 at 05:43:15PM +, John Smith wrote: > When trying to create a complex type, the parser produces an error. e.g. >CREAYE TYPE product AS (name varchar, price numeric); > produces > ERROR: parser: parse error at or near "as" Is that the *exact* command you're running? The word CREATE is misspelled, although that should give a different error: CREAYE TYPE product AS (name varchar, price numeric); ERROR: syntax error at or near "CREAYE" at character 1 LINE 1: CREAYE TYPE product AS (name varchar, price numeric); ^ If that's not the actual command, then please copy and paste what you're really doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1551: You need an import/export feature
On Thu, Mar 17, 2005 at 08:24:06PM +, Bobby wrote: > PostgreSQL version: 8.1 Do you mean 8.0.1? There is no 8.1; CVS HEAD is currently 8.1devel, and it's a long way from being released. > You guys badly need an import/export feature. Could you be more specific? Are you familiar with COPY and psql's \copy? http://www.postgresql.org/docs/8.0/interactive/sql-copy.html http://www.postgresql.org/docs/8.0/interactive/app-psql.html If you'd like additional functionality then please describe what you're looking for. Even better, submit a patch that implements it ;-) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1550: LOCK TABLE in plpgsql function doesn't work.
"Spencer Riddering" <[EMAIL PROTECTED]> writes: > When a LOCK TABLE statement is included in a plpgsql function it does not > actually lock the table. Sure it does. If it doesn't, your test case surely will not prove it; you cannot prove the existence or lack of existence of a lock in a test case with only one connection... I suspect your complaint really has to do with the fact that the transaction snapshot is established before the function is entered, and thus before the lock is taken. Pre-8.0, we did not advance the snapshot within functions, and so the commands within the function would all see a snapshot that predated the obtaining of the lock. Short answer: try 8.0. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1550: LOCK TABLE in plpgsql function doesn't work.
On Thu, Mar 17, 2005 at 08:48:54AM +, Spencer Riddering wrote: > When a LOCK TABLE statement is included in a plpgsql function it does not > actually lock the table. How did you determine that? It's not clear from the example you posted, and your function has its LOCK statements commented out. I ran simple tests in 7.4.7 and LOCK worked in a PL/pgSQL function. Here's an example, run from psql: CREATE TABLE foo (x integer); CREATE FUNCTION locktest() RETURNS void AS ' BEGIN LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE; RETURN; END; ' LANGUAGE plpgsql; BEGIN; SELECT locktest(); SELECT * FROM pg_locks; relation | database | transaction | pid | mode | granted --+--+-+---+---+- 16759 |17144 | | 26277 | AccessShareLock | t | | 19353 | 26277 | ExclusiveLock | t 19293 |17144 | | 26277 | ShareRowExclusiveLock | t (3 rows) If I try to acquire a conflicting lock in another transaction, it blocks and pg_locks then looks like this: relation | database | transaction | pid | mode | granted --+--+-+---+---+- 19293 |17144 | | 26274 | ShareRowExclusiveLock | f 16759 |17144 | | 26277 | AccessShareLock | t | | 19353 | 26277 | ExclusiveLock | t 19293 |17144 | | 26277 | ShareRowExclusiveLock | t | | 19354 | 26274 | ExclusiveLock | t (5 rows) > But, if prior to calling the function I execute a seperate statement using > the same connection and same transaction then the LOCK TABLE does work. > > I think the expectation is that LOCK TABLE should actually lock the table > even when included in a plpgsql function. > > I used JDBC (pg74.215.jdbc3.jar) to discover this behavior. Is it possible that when you called the function without executing anything beforehand, it was run in a transaction that ended sooner than you were expecting? That would release any locks the function had acquired. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
