[GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2
Hi, Has anyone tried to install Postgres on Windows Sever 2003 version R2? R2 is actually shipping as a 'new' Microsoft product- it's basically an interim update to Windows Server ( http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.mspx). I've installed Postgres on other versions of Windows with no problem, so I'm afraid that the error I'm seeing now is related to some great new 'feature' from Microsoft. Here's the error message returned by Postgres before install begins- Error binding the test network socket: 10013 Microsoft Antispyware has been turned off (closed the application) and Windows Firewall isn't running. There's no other AV or firewall software on this system yet. Any ideas on what might be going on? Jon
Re: [GENERAL] Performance Low Using the Prepare and Execute
On Sat, 2006-01-07 at 20:38 +, Marcos José Setim wrote: > I want to use the Prepare and Execute resources of PostgreSQL to > increment the performance of my SQL's. > $sSQL = 'INSERT INTO teste (nome) VALUES( ? )'; > > $oDB->Prepare( $sSQL ); The PREPARE documentation states:[1] Prepared statements have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, for example, if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable. Since an INSERT ... VALUES without a subselect or any applicable rules requires very little parsing, planning, or rewriting time, PREPARE/EXECUTE is unlikely to improve performance. -Neil [1] http://developer.postgresql.org/docs/postgres/sql-prepare.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Create one prepared function
On Sun, 2006-01-08 at 00:12 +, Marcos José Setim wrote: > I'd like that create functions in plpgsql with prepared SQL and plan > saved, to that the Postgresl increase the performance of executions. > > This is possible? plpgsql internally caches query plans the first time a function is invoked in a given session, so there is probably no (performance) reason to do it by hand. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] SQLData user-defined-types and getObject()
Hi All,
I am quite confused (PLEASE PLEASE Help), I cannot find anything on
the web). I read that you can declare a class that implements SQLData
(in this case I set up a class called Complex from the /src/tutorial
datatype that mimics the user-defined datatype in the db) and then set
the mapping appropriately (see below).
But it does not work ( I maybe doing something wrong)!!!
The "getObject" code throws:
Exception in thread "main" org.postgresql.util.PSQLException:
Unsupported Types value: 2,000
and the setObject code throws:
Exception in thread "main" java.lang.ClassCastException: java.lang.Class
(points to the getObject() line
ANY help would be much appreciated!
much thanks in advance.
-assad
Reference:
//Mapping type setup
java.util.Map map = db.getTypeMap();
if(map == null) map = new HashMap();
map.put("complex", Class.forName("Complex"));
db.setTypeMap(map);
map = db.getTypeMap();
and then I can set and get as follows:
//set info
Complex test = new Complex();
test.a = 5.6;
test.b = 3.4;
Statement stmt = db.createStatement();
PreparedStatement temp = db.prepareStatement("INSERT INTO
test_complex VALUES (DEFAULT, ?);");
temp.setObject(1,test);
temp.executeUpdate();
//I also tried with setObject with the type specified to
java.sql.TYPE_JavaObject
//get info
ResultSet rs = stmt.executeQuery("SELECT * FROM test_complex");
System.out.println("Got ");
while(rs.next()){
System.out.println("Got ");
ResultSetMetaData metaData = rs.getMetaData();
System.out.println("Type from SQL: " +
metaData.getColumnTypeName(2));
Object foo = (Object) rs.getObject(2, map);
if (foo instanceof Complex) {
Complex cp = (Complex)foo;
System.out.println("Got: " + cp + " from DB");
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Create one prepared function
Hi, I'd like that create functions in plpgsql with prepared SQL and plan saved, to that the Postgresl increase the performance of executions. This is possible? I would like to see a simple example of use this, if this is possible, i find for examples in the google, but the joined result is little direct and many dispersed. Very Thanks!! -- __ Marcos José Setim [EMAIL PROTECTED] http://www.linuxhard.org ---(end of broadcast)--- TIP 1: 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] E-mail harvesting on PG lists?
Carlos Moreno wrote: > Any comments? If it is the first option above, then it feels like > by definition there is absolutely nothing that can be done, now or > ever :-( I got an IMAP account with BurntMail.com. I belong to a dozen mailing lists, and haven't received any spam since getting the email account. They obviously do aggressive spam filtering, but as far as I know I'm getting all the email I should. An option to consider... -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PGError: server closed the connection unexpectedly
Dave Steinberg <[EMAIL PROTECTED]> writes: >> You're not using the same PG connection from two different threads, or >> fork()ing and trying to use the same connection in the parent and the >> child, or anything like that? > > Aha! In the ruby code, I am forking! I'll make the child reconnect > and see if that helps (I am almost sure this will fix it). This one I > should have guessed - I dealt with similar stuff in perl somewhat > recently. Yay! I thought it might be something like that. > The pg_dumpall problem... I'll crank the debug level and see if > there's anything interesting there, and if so I'll post about it again. You could also, if you have to, run the BSD equivalent of 'strace' (ktrace?) against the backend that pg_dump is connected to, and see what might be going on. 'strace' is the gun I pull out when logfiles aren't working for me. :) -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PGError: server closed the connection unexpectedly
You're not using the same PG connection from two different threads, or fork()ing and trying to use the same connection in the parent and the child, or anything like that? Aha! In the ruby code, I am forking! I'll make the child reconnect and see if that helps (I am almost sure this will fix it). This one I should have guessed - I dealt with similar stuff in perl somewhat recently. The pg_dumpall problem... I'll crank the debug level and see if there's anything interesting there, and if so I'll post about it again. Thanks Doug! -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] E-mail harvesting on PG lists?
Carlos Moreno wrote:
> today I notice a phishing e-mail ("Your PayPal account"), meaning
> that it took less than two weeks for my e-mail address to go from
> PG's mailing list to a spammers' database of addresses...
Normally you get turnaround times of less than two hours on this, so you
got a good deal.
---(end of broadcast)---
TIP 1: 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] COPY to
Ok thanks AndreasOn 1/7/06, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: Angshu Kar <[EMAIL PROTECTED]> schrieb:> Thanks Andreas. But how can I run this from the pgAdmin III Query tool in a> WinXP m/c?Sorry, i don't using pgAdmin nor windows... I mean, use the CLI-Interface psql.Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---TIP 6: explain analyze is your friend-- Ignore the impossible but honor it ... The only enviable second position is success, since failure always comes first...
Re: [GENERAL] E-mail harvesting on PG lists?
Magnus Hagander wrote: > archives.postgresql.org properly "hides" the addresses. If you think that spammers are unable to do s/ (at) /@/ you're living in a dream world. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] COPY to
Angshu Kar <[EMAIL PROTECTED]> schrieb: > Thanks Andreas. But how can I run this from the pgAdmin III Query tool in a > WinXP m/c? Sorry, i don't using pgAdmin nor windows... I mean, use the CLI-Interface psql. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PGError: server closed the connection unexpectedly
Dave Steinberg <[EMAIL PROTECTED]> writes: >>>My biggest problem is the lack of any real error message on the server. >>>I don't see anything wrong in the system logs, and there's no core >>> file in the /var/postgresql directory. >> Are you sure core files are enabled; i.e. the server is running with >> 'ulimit -c unlimited' ? > > Yes: > > $ whoami > _postgresql > $ ulimit -c > unlimited But does the startup script for PG set the limits as well? It's quite possible that the PG daemon startup sequence and logging in as the PG user go through different scripts. Also, make sure that you're looking in the right place for core dumps--OpenBSD may put them somewhere weird by default. [...] > That looks to me like a clean and normal exit. This is pointing more > and more towards the client in the ruby case, isn't it? Yeah, if the server were crashing its exit code would be greater than 127. Also, usually when a backend crashes, the postmaster takes the whole server down on the assumption that shared memory may have been corrupted. It doesn't sound like this is happening to you, which again points to a client problem. You're not using the same PG connection from two different threads, or fork()ing and trying to use the same connection in the parent and the child, or anything like that? -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Performance Low Using the Prepare and Execute
Hi, I want to use the Prepare and Execute resources of PostgreSQL to increment the performance of my SQL's. I do tests using the PHP and ADODB inserting 5000 registers and counting the time of execution. But the results was pratically identical. See below the results: Time of Execution (ADODB:: Prepare and Execute Manually) : 44.9510087967 Time of Execution (ADODB:: Prepare and Execute of ADODB): 47.6438999176 Time of Execution (ADODB:: Without Prepare): 47.6229438782 Test1 Code: $sSQL = 'EXECUTE teste(\'teste0...\')'; for ( $i = 0; $i < $iNTestes; $i++ ) $oDB->execute_query( $sSQL, __LINE__, __FILE__ ); Test2 Code: $sSQL = 'INSERT INTO teste (nome) VALUES( ? )'; $oDB->Prepare( $sSQL ); for ( $i = 0; $i < $iNTestes; $i++ ) $oDB->ExecutePrepare( Array( 'teste1...' ), __LINE__, __FILE__ ); Teste3 Code: $sSQL = 'INSERT INTO teste (nome) VALUES(\'teste2...\')'; for ( $i = 0; $i < $iNTestes; $i++ ) $bOk = $oDB->execute_query( $sSQL, __LINE__, __FILE__ ); The SQL that they use the Prepare and Execute would have to be lesser than the others? Thanks. -- __ Marcos José Setim [EMAIL PROTECTED] http://www.linuxhard.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] programming in pgsql
Hi Pgsql,Could anyone please advise whether the following program can be implemented using pgsql cursors/anythign else (or do we need some external scripts)?If yes, could you give please some function names etc? select A from Bcluster where pvalue = 0.3--say we get A=8 select B, A_child from CCluster where A = 8--If we get a A_child (say we get A_child=7) from the above query we search that value in CCluster table again and store the Bs in some place as: select B, A_child from CCluster where A = 7--We do this till we get all Bs and no more A_childs --We display and store the Bs for further usageThanks,AK
Re: [GENERAL] PGError: server closed the connection unexpectedly
My biggest problem is the lack of any real error message on the server.
I don't see anything wrong in the system logs, and there's no core
file in the /var/postgresql directory.
Are you sure core files are enabled; i.e. the server is running with
'ulimit -c unlimited' ?
Yes:
$ whoami
_postgresql
$ ulimit -c
unlimited
I did a 'vacuumdb -afz' just as
a shot in the dark, without affect. Pretty much all I see in the logs
is this:
LOG: unexpected EOF on client connection
This means a client is dying or closing its connection prematurely,
and would seem to be a different problem. It shouldn't ever cause the
server to crash.
If the server is crashing - I have absolutely no info to suggest that,
except possibly this: at a later time I did a tcpdump of one of these
sessions, for lack of better ideas, and saw this near the end:
16:32:19.523842 clam.int.geekisp.com.15245 >
morningsun.int.geekisp.com.postgresql: P 17389:18036(647) ack 9323 win
16384 (DF)
16:32:19.524289 morningsun.int.geekisp.com.postgresql >
clam.int.geekisp.com.15245: P 9323:9343(20) ack 18036 win 17376
(DF)
16:32:19.562544 clam.int.geekisp.com.15245 >
morningsun.int.geekisp.com.postgresql: P 18036:18041(5) ack 9343 win
16384 (DF)
**16:32:19.52 morningsun.int.geekisp.com.postgresql >
clam.int.geekisp.com.15245: F 9343:9343(0) ack 18041 win 17376
(DF)
16:32:19.567043 clam.int.geekisp.com.15245 >
morningsun.int.geekisp.com.postgresql: . ack 9344 win 16384
(DF)
16:32:19.649445 clam.int.geekisp.com.15245 >
morningsun.int.geekisp.com.postgresql: P 18041:18851(810) ack 9344 win
16384 (DF)
16:32:19.649468 morningsun.int.geekisp.com.postgresql >
clam.int.geekisp.com.15245: R 545360451:545360451(0) win 0 (DF)
16:32:19.649652 clam.int.geekisp.com.15245 >
morningsun.int.geekisp.com.postgresql: F 18851:18851(0) ack 9344 win
16384 (DF)
16:32:19.649665 morningsun.int.geekisp.com.postgresql >
clam.int.geekisp.com.15245: R 545360451:545360451(0) win 0 (DF)
The '*' above is my own, to highlight the interesting part.
Morningsun is the server, clam is the ruby client. Based on this, I
think the server *might* be dying first (hard to tell), since morningsun
is the one who sends the FIN packet and thereby closes the connection.
(Sorry for the horrible wrapping).
Again, having trouble making sure.
Googling turned up a few reports suggesting bad hardware, or corrupted
indexes, but I don't think that's the case here.
Any starting points or ideas would be greatly appreciated.
Make sure the server is able to dump a core file, and perhaps crank up
the logging level.
Just to verify that a core dump is possible for the postgresql user, I
wrote a tiny C program that just calls abort(). Sure enough, running it
gives me a core file.
I cranked the debug level up to 5 (man that's a lot), and here's what I
think is the relevent chunk:
STATEMENT: UPDATE
job_members SET "start_time" = NULL, "exit_code" = NULL, "create_time" =
'2006-01-07 17:10:31', "job_id" = 30, "command" = '---
!ruby/object:GeekISP::ShellCommand
args:
command: /bin/ls /tmp/
exit_code:
logger: !ruby/object:Logger
datetime_format:
level: 0
logdev: !ruby/object:Logger::LogDevice
dev: !ruby/object:File {}
filename:
/mnt/scratch/dave/control_panel/trunk/config/../log/test.log
shift_age: 0
shift_size: 1048576
progname:
run_as:
stderr:
stdin:
stdout: ', "finish_time" = NULL, "last_update_time" = NULL,
"host_id" = 4, "status" = 'in_progress', "output" = NULL WHERE
job_member_id = 105
DEBUG: name:
unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid:
10290432/1/0, nestlvl: 1, children: <>
STATEMENT: UPDATE
job_members SET "start_time" = NULL, "exit_code" = NULL, "create_time" =
'2006-01-07 17:10:31', "job_id" = 30, "command" = '---
!ruby/object:GeekISP::ShellCommand
args:
command: /bin/ls /tmp/
exit_code:
logger: !ruby/object:Logger
datetime_format:
level: 0
logdev: !ruby/object:Logger::LogDevice
dev: !ruby/object:File {}
filename:
/mnt/scratch/dave/control_panel/trunk/config/../log/test.log
shift_age: 0
shift_size: 1048576
progname:
run_as:
stderr:
stdin:
stdout: ', "finish_time" = NULL, "last_update_time" = NULL,
"host_id" = 4, "status" = 'in_progress', "output" = NULL WHERE
job_member_id = 105
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
LOG: disconnection:
session time: 0:00:00.84 user=geekispv2 database=geekisp-v2-test
host=192.168.4.38 port=31992
DEBUG: exit(0)
<% 7303>DEBUG: reaping dead processes
<% 7303>DEBUG: server process (PID 9155) exited with exit code 0
That looks to me like a clean and normal exit. This is pointing more
and more towards the client in the ruby case, isn't it?
Regards,
--
Dave St
Re: [GENERAL] COPY to
Thanks Andreas. But how can I run this from the pgAdmin III Query tool in a WinXP m/c?On 1/7/06, A. Kretschmer < [EMAIL PROTECTED]> wrote:am 07.01.2006, um 14:13:28 -0600 mailte Angshu Kar folgendes: > Hi Pgsql,>> I want to copy the output of a SELECT query onto a text file. I'm trying to> use the COPY command for that as :>> COPY (SELECT ) to 'outfile'Wrong. \o output.txtselect ...\oAnd now you have the result in 'output.txt'.HTH, Andreas--Andreas Kretschmer(Kontakt: siehe Header)Heynitz: 035242/47212, D1: 0160/7141639GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe===---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster -- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...
Re: [GENERAL] PGError: server closed the connection unexpectedly
Dave Steinberg <[EMAIL PROTECTED]> writes: > My biggest problem is the lack of any real error message on the server. > I don't see anything wrong in the system logs, and there's no core > file in the /var/postgresql directory. Are you sure core files are enabled; i.e. the server is running with 'ulimit -c unlimited' ? > I did a 'vacuumdb -afz' just as > a shot in the dark, without affect. Pretty much all I see in the logs > is this: > > LOG: unexpected EOF on client connection This means a client is dying or closing its connection prematurely, and would seem to be a different problem. It shouldn't ever cause the server to crash. > Googling turned up a few reports suggesting bad hardware, or corrupted > indexes, but I don't think that's the case here. > > Any starting points or ideas would be greatly appreciated. Make sure the server is able to dump a core file, and perhaps crank up the logging level. -Doug ---(end of broadcast)--- TIP 1: 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] PGError: server closed the connection unexpectedly
Hello list, I've been working a bit today to resolve the error I'm seeing mentioned in the title, and I was hoping you might have some insight. I've managed to semi-reliably reproduce this based on two different tasks: - a pg_dumpall from my backup server will throw this, but not always on the same database. Sometimes it seems to get 'stuck' on one, but later if I try it again it'll throw the error on a different DB. The pg_dumpall appears to work perfectly when run locally. - A unit test for a Rails app I'm developing will consistently throw this error, though oddly I can run the problem statement in psql, over the network, without problem. My biggest problem is the lack of any real error message on the server. I don't see anything wrong in the system logs, and there's no core file in the /var/postgresql directory. I did a 'vacuumdb -afz' just as a shot in the dark, without affect. Pretty much all I see in the logs is this: LOG: unexpected EOF on client connection Googling turned up a few reports suggesting bad hardware, or corrupted indexes, but I don't think that's the case here. Any starting points or ideas would be greatly appreciated. Basic server info: - Less than 3 months old - OpenBSD 3.8, patched fully, x86 w/ a P4 - 2G of Ram, ~1.5G free usually - /var/postgresql partition has about 8G free, and is backed by a AMI hardware raid-1 array. - PostgreSQL version 8.0.3 on server and clients, built locally from OpenBSD ports - ruby-postgres adapter version 0.7.1 used in the rails project Special OS Config bits: - kern.seminfo sysctl tree: kern.seminfo.semmni=256 kern.seminfo.semmns=2048 kern.seminfo.semmnu=30 kern.seminfo.semmsl=60 kern.seminfo.semopm=100 kern.seminfo.semume=10 kern.seminfo.semusz=100 kern.seminfo.semvmx=32767 kern.seminfo.semaem=16384 - Bits from _postgresql's ulimit: $ ulimit -a time(cpu-seconds)unlimited file(blocks) unlimited coredump(blocks) unlimited data(kbytes) 1048576 stack(kbytes)8192 lockedmem(kbytes)635424 memory(kbytes) 1905136 nofiles(descriptors) 768 processes532 - postgresql.conf changes: max_connections = 200 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_duration = true stats_start_collector = true stats_command_string = false stats_block_level = false stats_row_level = false Thanks in advance! PS - My apologies if this is a dup! -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY to
am 07.01.2006, um 14:13:28 -0600 mailte Angshu Kar folgendes: > Hi Pgsql, > > I want to copy the output of a SELECT query onto a text file. I'm trying to > use the COPY command for that as : > > COPY (SELECT ) to 'outfile' Wrong. \o output.txt select ... \o And now you have the result in 'output.txt'. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Oracle DB Worm Code Published
Christopher Browne <[EMAIL PROTECTED]> writes: >> A recent article about an Oracle worm: >> http://www.eweek.com/article2/0,1895,1880648,00.asp >> got me wondering. > PostgreSQL doesn't allow network access, by default, which more than > makes up for that. You would have to both alter postgresql.conf (to make the postmaster listen for anything except local connections) and alter pg_hba.conf to let people in. Of course, if you were fool enough to set pg_hba.conf to allow "trust" connections from the whole net, you'd have a door open even wider than Oracle's. But I hope that's not common. A worm can't be successful unless there's a fairly large population of vulnerable machines. I am sure that there are *some* PG installations out there that are wide open, but I doubt there are enough to make a worm viable. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] COPY to
Hi Pgsql,I want to copy the output of a SELECT query onto a text file. I'm trying to use the COPY command for that as :COPY (SELECT ) to 'outfile'But its throwing some error. Is it possible? Else, are there any means to do it? Thanks,AK-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...
Re: [GENERAL] 'Official' definition of ACID compliance?
On Thursday 05 January 2006 17:04, Russ Brown wrote:
> On Thu, 5 Jan 2006 22:25:21 +0100
>
> Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> > Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
> > > But it's not consistent. Imagine we do the one where we take one
> > > from peter and give it to paul. If paul's account is stored in an
> > > int, and is at 2147483647, and we add one, it does not increment,
> > > and it does not cause an error that will force a transaction to
> > > roll back.
> >
> > The effects of the commands on the database are not sensible with
> > respect to the intent of the commands, but the state of the database
> > is consistent both before and afterwards with respect to the
> > integrity constraints defined within the database. That's what this
> > is all about. ACID is about transaction processing, not about SQL
> > data type semantics.
>
> That argument holds true when you consider two key points in a
> transaction: before and after. But there is also a third: the
> transaction itself. i.e. the actual changes that are being made to the
> database. If you take the example given earlier about peter and paul,
> yes the database it in a consistent state both before and after the
> transaction. But it's *not* in a consistent state when compared with
> the transaction itself. The transaction asked that a field value be
> incremented, and after the transaction concluded this had not
> happened, yet the transaction was committed. ACID
> compliance requires that either all or none of the operations in the
> transaction happen. In this case one of them does not.
The problem here is that your asking the value to be incremented however your
definition of the columns data type also asks that it be kept lower than a
given value (based on mysql built in assumptions). Think about if you created
a bigint column in a postgresql table and then defined a trigger/rule to
modify any value larger than int acceptable to the int max. This doesn't
break ACID compliance, your database is in a consitant state and it has done
with the data what it has been defined to do. mysql just does this data
manipulation piece for you (even if you dont want it), but thats not
inherently ACID-incompliant anymore than how they handle timestamp fields.
That said see nearby posts about row level constraints and transactions
involving mixed table types that can't actually be rolled back if you want to
find ways that mysql breaks acid compliance.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: 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] Reordering columns in a table
You should be able to do this now using pg_depend, it would just take a bit of
leg-work. Pretty sure it would be easier than solving physical/logical
attribute separation. Someone writing a pg_list_all_dependencies function
would make for a really good head start... I wonder if the newsysviews
provides anything like that.
Robert Treat
On Friday 06 January 2006 22:34, Ian Harding wrote:
> As I recall, the MS SQL Server draggy droppy diagrammer tool made it
> seem trivial to rearrange columns did the same thing. It just
> generated SQL statements to:
>
> Begin transaction
> select data in new order into a new table
> drop dependent objects
> drop old table
> rename new table
> re-create dependent objects
> end transaction
>
> It seemed kinda squirrelly to me, but it worked most of the time since
> MSSQL Server had a good dependency tracking thingie. However, I would
> not really call it a feature of the DBMS. I would call it a bolted on
> utility.
>
> On 1/6/06, Scott Ribe <[EMAIL PROTECTED]> wrote:
> > > I would assume
> > > that all dependent database objects are also dropped when you drop the
> > > table, so you'd have to recreate all of your foreign keys (both
> > > directions) and triggers etc.?
> >
> > Basically. I try to keep my DDL scripts organized in a way that makes
> > this easy. Of course an automated tool could do this as well. For
> > instance I used to use the products from Embarcadero to maintain Sybase
> > databases, and their design tool would create all the DDL needed to
> > update a live database to match the current design. Of course, one
> > experience with a bug and I learned to have it show me the script and
> > read it carefully before proceeding ;-) (Hey, I'm not stupid, it was just
> > a development db that I hosed!)
> >
> >
> > --
> > Scott Ribe
> > [EMAIL PROTECTED]
> > http://www.killerbytes.com/
> > (303) 665-7007 voice
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 2: Don't 'kill -9' the postmaster
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] E-mail harvesting on PG lists?
I created an account for perl-cpan and it got hit with spam/phishing attempts in less than a week. There's not a lot that can be done about it. It's a losing battle to try and fight. There are some things you can do, but it won't be 100% effective. The closer you get to 100% effective, the more likely you are to throw the baby out with the bathwater. I started using dedicated addresses a few years ago. Anytime I sign up for something, I use an address dedicated for that purpose. Then, when I start seeing spam patterns, I know where the address was used. In the case of mailing lists, there's not much to hide. However, when you sign up for something with a legit store, and then 2 or 3 months later you start getting bombarded with spam having nothing to do with that store -- it's a pretty safe bet where the spammer got your address (unless you use a very easy to guess address like a simple first name or something). The other problem is dictionary attacks. There are distributed networks of bots that do nothing except try a dictionary of names against your mailserver. You can see how coordinated they are when you are getting dictionary scans from IP addresses all over the globe, starting with A, and not overlapping words. They are getting more devious too. I found one that had a bug in their tool so it was obvious the connections were linked and they overlapped names every so often (unless it was a single bot net running two separate lists, which is also possible). It's ugly. No matter how you slice. Greg
Re: [GENERAL] Oracle DB Worm Code Published
> A recent article about an Oracle worm: > http://www.eweek.com/article2/0,1895,1880648,00.asp > got me wondering. > Could a worm like this infect a PostgreSQL installation? > It seems to depend on default usernames and passwords - > and lazy DBAs, IMO. > Isn't it true that PostgreSQL doesn't have any default user/password? > Is this an issue we should be concerned about, at some level? PostgreSQL doesn't allow network access, by default, which more than makes up for that. -- "cbbrowne","@","cbbrowne.com" http://cbbrowne.com/info/slony.html "...Yet terrible as Unix addiction is, there are worse fates. If Unix is the heroin of operating systems, then VMS is barbiturate addiction, the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your sinuses with lucite and letting it set.) You owe the Oracle a twelve-step program." --The Usenet Oracle ---(end of broadcast)--- TIP 1: 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] The connection is dead
What does your code look like? And have you tried using Npgsql instead of the ODBC driver? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Saturday, January 07, 2006 4:39 AM To: pgsql-general@postgresql.org Subject: [GENERAL] The connection is dead Hi, I'm running PostgreSQL 8.1.1 on the WindowsXP box with following information: 1. Programming Language C# MS .NET Framework 2. ADO NET connection through PostgresODBC 3. Using "Connection.BeginTransaction(IsolationLevel.ReadCommitted) and Transaction.Commit to update database. 4. Update record by record. 5. There is no problem if the records is less than 1000 records. 6. If records is over 1000 records, I got the error message "The connection is dead". How can I configure the PostgreSQL 8.1.1 to handle it. Thank you for any suggestion. Pairat ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.15/223 - Release Date: 1/6/2006 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Oracle DB Worm Code Published
> A recent article about an Oracle worm: > http://www.eweek.com/article2/0,1895,1880648,00.asp > got me wondering. > Could a worm like this infect a PostgreSQL installation? > It seems to depend on default usernames and passwords - and > lazy DBAs, IMO. > Isn't it true that PostgreSQL doesn't have any default user/password? That's true. however, PostgreSQL ships by default with access mode set to "trust", which means you don't *need* a password. And I bet you'll find the user being either "postgres" or "pgsql" in 99+% of all installations. We do, however, ship with network access disabled by default. Which means a worm can't get to it, until you enable that. But if you enable network access, and don't change it from "trust" to something else (such as md5), then you're wide open to this kind of entry. (Just create an untrusted PL and hack away - assuming those binaries are inthere, but I bet they are in most installations) //Magnus ---(end of broadcast)--- TIP 1: 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] Oracle DB Worm Code Published
A recent article about an Oracle worm: http://www.eweek.com/article2/0,1895,1880648,00.asp got me wondering. Could a worm like this infect a PostgreSQL installation? It seems to depend on default usernames and passwords - and lazy DBAs, IMO. Isn't it true that PostgreSQL doesn't have any default user/password? Is this an issue we should be concerned about, at some level? TJ O'Donnell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] E-mail harvesting on PG lists?
Bruce Momjian writes: > Carlos Moreno wrote: >> Any comments? If it is the first option above, then it feels like >> by definition there is absolutely nothing that can be done, now >> or ever :-( > Yes, I came to that conclusion long ago. Aggressive spam filtering is about the only thing that keeps email workable at all anymore :-(. The idea of keeping your address hidden is not workable and never really has been IMHO. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Role Permissions
"Craig" <[EMAIL PROTECTED]> writes: > I am trying to prevent anyone that inhertis from role1 to not be able to = > select from any database table, unless they execute a function that I = > have provided. How do I setup the security for this?=20 You need to mark the function as SECURITY DEFINER, which means that it runs with its creator's permissions. By default a function runs with the caller's permissions. (Yeah, SECURITY DEFINER is a pretty obscure name for this. It's what the SQL spec requires though :-() regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] E-mail harvesting on PG lists?
> > This is truly worrisome... I wonder if spammers today are > basically > > subscribing to mailing lists so that they receive the > e-mails (seems > > like a very obvious trick), or if they're moving to the > next level of > > "decrypting" the "encrypted / anti-spam" form of e-mail > addresses (the > > way they're displayed on the mailing list web site) > > Our email lists are mirrored onto web sites like Google, so I > am thinking they got it that way. archives.postgresql.org properly "hides" the addresses. However, they are mirrored to Usenet News, where anybody can pick them up. Much easier that way, I bet that's what most use. > > Any comments? If it is the first option above, then it feels like > > by definition there is absolutely nothing that can be done, now or > > ever :-( > > Yes, I came to that conclusion long ago. That's the bottom line, though. Spamfilters help, but only part of the way. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] E-mail harvesting on PG lists?
Carlos Moreno wrote:
>
> This is worrisome...
>
> I decided to create a separate account for my subscription to PG's
> mailing lists (to avoid all replies bouncing back due to my strict
> whitelist anti-spam filter) -- I created the account on Dec 22, and
> today I notice a phishing e-mail ("Your PayPal account"), meaning
> that it took less than two weeks for my e-mail address to go from
> PG's mailing list to a spammers' database of addresses... Needless
> to say that I have not used this e-mail address (but really, really
> really 100% absolute certainty that I have not used it in any single
> instance), other than to post a couple messages in here.
>
> This is truly worrisome... I wonder if spammers today are basically
> subscribing to mailing lists so that they receive the e-mails (seems
> like a very obvious trick), or if they're moving to the next level
> of "decrypting" the "encrypted / anti-spam" form of e-mail addresses
> (the way they're displayed on the mailing list web site)
Our email lists are mirrored onto web sites like Google, so I am
thinking they got it that way.
> Any comments? If it is the first option above, then it feels like
> by definition there is absolutely nothing that can be done, now
> or ever :-(
Yes, I came to that conclusion long ago.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] E-mail harvesting on PG lists?
This is worrisome...
I decided to create a separate account for my subscription to PG's
mailing lists (to avoid all replies bouncing back due to my strict
whitelist anti-spam filter) -- I created the account on Dec 22, and
today I notice a phishing e-mail ("Your PayPal account"), meaning
that it took less than two weeks for my e-mail address to go from
PG's mailing list to a spammers' database of addresses... Needless
to say that I have not used this e-mail address (but really, really
really 100% absolute certainty that I have not used it in any single
instance), other than to post a couple messages in here.
This is truly worrisome... I wonder if spammers today are basically
subscribing to mailing lists so that they receive the e-mails (seems
like a very obvious trick), or if they're moving to the next level
of "decrypting" the "encrypted / anti-spam" form of e-mail addresses
(the way they're displayed on the mailing list web site)
Any comments? If it is the first option above, then it feels like
by definition there is absolutely nothing that can be done, now
or ever :-(
Carlos
--
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] write on screen
Hi. I have a problem - I run pl/sql script on postgresql base using command line in windows and I need to view some data in this command line, but I don't know how to send them in the script.Pleas help ---(end of broadcast)--- TIP 1: 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] Role Permissions
Hi I am trying to understand how permissions work with plpgsql functions. I have created a role (lets call it role1) and assigned EXECUTE to a function (lets call it func_1). In func_1, I select data from tableA. I have then created another role (role2) that inherits from role1. When I login as role2 and issue "select * from func_1(...);" it comes back with the following error: "ERROR: permission denied for relation tableA". I am trying to prevent anyone that inhertis from role1 to not be able to select from any database table, unless they execute a function that I have provided. How do I setup the security for this? I come from an MS SQL background and in that RDBMS you can grant execute to a stored procedure and any objects that are accessed in the proc work, even if the user has no direct permissions to those objects. Any help will be greatly appreciated Craig
[GENERAL] The connection is dead
Hi, I'm running PostgreSQL 8.1.1 on the WindowsXP box with following information: 1. Programming Language C# MS .NET Framework 2. ADO NET connection through PostgresODBC 3. Using "Connection.BeginTransaction(IsolationLevel.ReadCommitted) and Transaction.Commit to update database. 4. Update record by record. 5. There is no problem if the records is less than 1000 records. 6. If records is over 1000 records, I got the error message "The connection is dead". How can I configure the PostgreSQL 8.1.1 to handle it. Thank you for any suggestion. Pairat ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
