|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Thursday, 25 January 2007
|
| COPY with no WAL, v2 Simon Riggs 05:17:38 |
| | VERSION 2, with all changed made as requested to date.
As discussed on -hackers, its possible to avoid writing any WAL at all
for COPY in these circumstances:
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php
and again recently.
BEGIN;
CREATE TABLE foo..
COPY foo...
COMMIT;
BEGIN;
TRUNCATE foo..
COPY foo...
COMMIT;
The enclosed patch implements this, as discussed. There is no user
interface to enable/disable, just as with CTAS and CREATE INDEX; no
docs, just code comments.
This plays nicely with the --single-transaction option in psql to allow
fast restores/upgrades.
YMMV but disk bound COPY will benefit greatly from this patch, some
tests showing 100% gain. COPY is still *very* CPU intensive, so some
tests have shown negligible benefit, fyi, but that isn't the typical
case.
While testing this, I realised something: small COPY commands get no
benefit at all, but larger ones do. When we do a small normal COPY the
data stays in cache, but the WAL is written to disk and fsynced. When we
do a small fast COPY, no WAL is written, but the data is written to disk
and fsynced. With COPY, WAL and data are roughly same size, hence no I/O
benefit. With larger COPY statements, benefit is very substantial.
Applies cleanly to CVS HEAD, passes make check.
I enclose a test case that shows whether the test has succeeded by
reading the WAL Insert pointer before/after each COPY. This has been
written in such a way that we could, if we wanted to, include a new
regression test for this. There is a function that returns an immutable
value if the test passes, rather than simply showing the WAL insert
pointer which would obviously vary between tests. The tests enclosed
here *also* include the WAL insert pointer so you can manually/visibly
see that the enclosed patch writes no WAL at appropriate times.
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgres
Do we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| |  5 answers | Add comment |
|
| Cannot Restart PostgreSQL-8.1.4 Rich Shepard 04:52:24 |
| | I had a problem with SQL-Ledger running on the local httpd that traced
back to some crufty old libpg.so* from 2003 and 2004 in /usr/local/lib. I
removed those (saved them, actually), ran ldconfig, then restarted both
httpd and postgresql. Unfortunately, the latter really has not started
despite indicating on the console that it has.
Postgres-8.1.4 installed.
Here're the libaries in /usr/lib/:
[rshepard@salmo ~]$ ll /usr/lib/libpq*
-rw-r--r-- 1 root root 149728 2006-05-24 15:06 /usr/lib/libpq.a
lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so ->
libpq.so.4.1*
lrwxrwxrwx 1 root root 12 2006-01-27 10:22 /usr/lib/libpq.so.3 ->
libpq.so.3.1*
-rwxr-xr-x 1 root root 110586 2006-01-26 09:49 /usr/lib/libpq.so.3.1*
lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so.4 ->
libpq.so.4.1*
-rwxr-xr-x 1 root root 111532 2006-05-24 15:06 /usr/lib/libpq.so.4.1*
And, in case postgres is looking in /usr/local/lib/ it has:
[rshepard@salmo ~]$ ll /usr/local/lib/libpq*
lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so ->
/usr/lib/libpq.so.4.1*
lrwxrwxrwx 1 root root 21 2007-01-24 10:35 /usr/local/lib/libpq.so.3 ->
/usr/lib/libpq.so.3.1*
lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so.4 ->
/usr/lib/libpq.so.4.1*
When I run '/etc/rc.d/rc.postgresql start' it returns 'Starting
PostgreSQL: ok', but '/etc/rc.d/rc.postgresql status' returns 'pg_ctl:
neither postmaster nor postgres running' which is true.
I don't know what I did to break the installation, but I would greatly
appreciate help getting it running again ASAP.
TIA,
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
---------------------------(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
|
| | 11 answers | Add comment |
|
| column insert/alter got me stumped! John Smith 00:39:13 |
| | guys,
just wanna change value of 1 existing column
# insert into tablename (columnname) values ('value');
...works
# select columnname from tablename where columnname='value';
...works
# insert into tablename (columnname) values ('value') select
columnname from tablename where columnname='value';
or
# insert into tablename (columnname) values ('value') where columnname='value';
...combinations don't work
# alter table tablename alter column columnname set value='value';
...doesn't work either
embarrassingly simple? pgadmin III thinks so
jzs
http://www.postgresql.org/docs/8.1/static/sql-insert.html
http://www.postgresql.org/docs/8.0/static/sql-altertable.html
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| Re: Installing PostgreSQL under Cpanel Erick Papa 00:39:13 |
| | There are a couple of PostgreSQL tutorials around the web to make it
work with WHM.
I have followed them. Downloaded the *.rpm files and installed them.
Then gone
into my WHM (https://myserver:2087) and enabled the config, and set up
the postgres user with an "su" command "adduser postgres".
Now what?
1. Where's the interactive shell? How can I start creating a database,
creating users, testing things out?
2. How do I start the service? How should I set it up to restart
automatically if (a) the service fails (b) machine reboots?
I looked at the Documentation
(http://www.postgresql.org/docs/8.2/interactive/config-setting.html)
and could not find one intuitive instruction to actually get cracking
with PostgreSQL without getting all configgy.
I'd appreciate some pointers.
Thanks!
---------------------------(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
|
| | 27 answers | Add comment |
|
| Cannot connect to one specific postgres database on a server Matt Busby 00:39:13 |
| | Hello,
I am having trouble connecting to one specific database on my server. I
can connect to the server via pgadmin and have pgadmin display 7 or 8
databases I have running on the server. I can successfully connect to
all of the databases except one. This particular database I am trying to
connect to will just cause pgadmin to hang/crash
I have been using pgadmin for over 2 years to connect to this particular
database that wont connect now. This just started happening a week ago
and is frustrating me so bad! I have installed/reinstalled/uninstalled
about every version on pgadmin with no success. I restarted postgres on
the server with no success.
My server is running linux, and I am using windows version of pgadmin.
I WOULD REALLY APPRECIATE ANY HELP!!! THANKS SO MUCH!!!
Matt Busby
|
| | 5 answers | Add comment |
Wednesday, 24 January 2007
|
| Example of RETURNING clause to get auto-generated keys from INSERT Ken Johanson 23:43:02 |
| | Greetings,
I am looking into possibly contributing some code for one of the
existing PG drivers, that will allow us to, after INSERT, get a
ResultSet containing the server generated keys (sequences or other).
I've been told that (short of implementing a new V4 server protocol) the
most effective way to do this, may be to use PG's RETURNING clause.
However I could really use some example queries, since I'm not
proficient enough with PG and this clause to know how to get the values.
I do know that the query should:
-support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4),
should return a result with 2 rows containing the new keys (one for each
column the users declares).
-query the values atomically (so that insert by another client won't
skew the curval / sequence) (obvious but deserves mention)
-ideally be predictable - just in case the sequence doesn't use a
increment value of one, or if some other non-sequence (triggers) or
numeric (uuids) generator is used.
-ideally not require parsing the user INSERT query (for table names
etc), though I expect that (in order to use RETURNING) I will have to
append to it.
The API I'd implement this for (jdbc), does require us to declare what
columns we are interested in getting generated keys for, so that might
preclude needing resultset metadata to know which columns have server
generated keys.
So if anyone can give SQL samples of how to best make this work, I would
be very much appreciative.
Thanks,
Ken
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | 3 answer | Add comment |
|
| capacity of tables Guillermo Arias 23:39:10 |
| | Hello, i am Guillermo Arias, from Peru. I have a doubt about capacity
of tables.
I am developing a software for accountants, and my principal problem
is about the table for the vouchers. I have to decide to make a table
for each year or only one table for all the years.
This table has 11 fields: varchar(10) and 2 fields: numeric (12,2) and
is intended to have 900,000 records per year x 13 years = 11'700,000
records
What can you suggest me? i do not want the system to be slow using
this table.
thanks
guillermoariast@hotmail.com
---------------------------------------------------------------------
Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.com
Join Linux Discussions! --> ; http://Community.LinuxWaves.com
|
| | 3 answer | Add comment |
|
| Linuxworld Toronto, April 30 - May 2 Robert Bernier 23:14:34 |
| | Is anybody planning to attend this and set up a booth?
http://www.it360.ca/
Robert
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 5 answers | Add comment |
|
| Re: Cannot Restart PostgreSQL-8.1.4 -- SOLVED! Rich Shepard 22:38:02 |
| | On Wed, 24 Jan 2007, Tom Lane wrote:
Have you looked in the postmaster log?
Tom,
I went looking for it before writing, but did not find it. Now I have.
The ownership and permissions of /var/lib/pgsql and /var/lib/postgresql were
FUBAR.
I changed the permissions and it's now running.
Thanks,
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
---------------------------(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
|
| | Add comment |
|
| weird buildfarm failures on arm/mipsel and --with-tcl Stefan Kaltenbrunner 22:24:20 |
| | one of my new buildfarm boxes (an Debian/Etch based ARM box) is
sometimes failing to stop the database during the regression tests:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=quagga&dt=2007-01-08%2003:03:03
this only seems to happen sometimes and only if --with-tcl is enabled on
quagga.
lionfish (my mipsel box) is able to trigger that on every build if I
enable --with-tcl but it is nearly impossible to debug it there because
of the low amount of memory and diskspace it has. (two consecutive
failures will run the kernel out of memory due to the resources consumed
by the still running processes).
After the stopdb failure we still have those processes running:
pgbuild 3389 0.0 1.5 39632 4112 ? S 06:14 0:03
/home/pgbuild/pgbuildfarm/HEAD/inst/bin/postgres -D data
pgbuild 3391 0.0 0.9 39632 2540 ? Ss 06:14 0:00
postgres: writer process
pgbuild 3392 0.0 0.5 11220 1348 ? Ss 06:14 0:00
postgres: stats collector process
pgbuild 3488 0.0 2.4 43640 6300 ? Ss 06:15 0:01
postgres: pgbuild pl_regression [local] idle
pgbuild 3489 0.0 0.0 0 0 ? Z 06:15 0:00
[postgres] <defunct>
Any ideas on how to debug that any further ?
Stefan
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 5 answers | Add comment |
|
| [pgsql-patches] pthread option of msvc build. Hiroshi Saito 22:24:05 |
| | Hi Magnus-san.
I am trying simple construction by operating config.pl.
It has changed wonderfully now. however, I do not use ecpg,
and see the simplest construction. At that time, even pthread
might not be needed. It was simple.
Please consider this.
P.S)
I can't catch up with your quick work. However, I will try the debugging
execution with VS2005 by the arrangement option. tools/msvc will
surely facilitate debugging.!
Thanks.
Regards,
Hiroshi Saito
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 4 answer | Add comment |
|
| Default permissisons from schemas Stephen Frost 22:11:43 |
| | Greetings,
* Stephen Frost (sfrost@snowman.net) wrote:> It seems unlikely that I'm going to have time at the rate things are> going but I was hoping to take a whack at default permissions/ownership> by schema. Kind of a umask-type thing but for schemas instead of roles> (though I've thought about it per role and that might also solve the> particular problem we're having atm).
Following up on my reply to Joshua, what I'd like to propose is, for
comments and suggestions:
ALTER SCHEMA name [ [ WITH ] [ DEFAULT ] option [ ... ] ]
where option can be:
{ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | EXECUTE }
[,...] | ALL [ PRIVILEGES ] }
TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
} [, ...]
OWNER role
pg_namespace would be modified to have two new columns,
nspdefowner oid, and nspdefacl aclitem[]. When NULL these would have
no effect. When not-null the 'nspdefowner' would be the owner of all
objects created in the schema. When not-null the 'nspdefacl' would be
the initial acl for the object (modified for what grants are valid for
the specific type of object). These can only be changed by the schema
owner and the 'OWNER role' must have create permissions in the schema.
Ideally this would be checked when the ALTER SCHEMA is issued and then
a dependency created for that. If that's not possible today then the
rights check would be done when an object creation is attempted,
possibly with a fall-back to check the current user's rights.
The defaults would be NULL for these so there would be no change in
behaviour unless specifically asked for.
I believe this would cover the following to-do item:
Allow GRANT/REVOKE permissions to be inherited by objects based on
schema permissions
Comments?
Thanks,
Stephen
|
| | 13 answers | Add comment |
|
| About PostgreSQL certification Iannsp 21:41:42 |
| | Hello,
I did like to know what you think about the postgresql certifications
provided for
PostgreSQL CE
http://www.sraoss.co.jp/postgresql-ce/news_en.html
CertFirst
http://www.certfirst.com/postgreSql.htm
My question is about the validate of this certification for the clients.
Make difference to be certified?
thanks for advanced.
Ivo Nascimento.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 19 answers | Add comment |
|
| how to read bytea field marcelo Cortez 18:53:23 |
| | folks
help me ,i cant read bytea type field's.
how to convert bytea to text or varchar ?
when using bytea types?
any clue be appreciated
best regards
mdc
__________________________________________________
Pregunt . Respond . Descubr .
Todo lo que quer as saber, y lo que ni imaginabas,
est en Yahoo! Respuestas (Beta).
Probalo ya!
http://www.yahoo.com.ar/respuestas
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 13 answers | Add comment |
|
| XML type in PostgreSQL 8.3 Peter Eisentraut 18:45:33 |
| | PostgreSQL 8.3 will come with a native xml type and support functions.
It would be nice if the JDBC driver could also make the appropriate
additions for handling this type. Java 6 adds the java.sql.SQLXML
interface to encapsulate values of that type, along with
ResultSet.getSQLXML and so on. Documentation is here:
http://java.sun.com/javase/6/docs/api/java/sql/SQLXML.html
The xml type in PostgreSQL CVS head should be fully functional to the
extent that one would need for developing this support. Initial
documentation is here:
http://developer.postgresql.org/pgdocs/postgres/datatype-xml.html
Maybe someone wants to tackle this, or at least make a note of it for
the future. Let me know how I can help.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 3 answer | Add comment |
|
| Updateable cursors FAST PostgreSQL 18:02:15 |
| | We are trying to develop the updateable cursors functionality into
Postgresql. I have given below details of the design and also issues we are
facing. Looking forward to the advice on how to proceed with these issues.
Rgds,
Arul Shaji
1. Introduction
--------------
This is a combined proposal and design document for adding updatable
(insensitive) cursor capability to the PostgreSQL database.
There have already been a couple of previous proposals since 2003 for
implementing this feature so there appears to be community interest in doing
so. This will enable the following constructs to be processed:
UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
This has the effect of users being able to update or delete specific rows of
a table, as defined by the row currently fetched into the cursor.
2. Overall Conceptual Design
-----------------------------
The design is considered from the viewpoint of progression of a command
through the various stages of processing, from changes to the file gram.y
to implement the actual grammar changes, through to changes in the Executor
portion of the database architecture.
2.1 Changes to the Grammar
------------------------------
The following changes will be done to the PostgreSQL grammar:
UPDATE statement has the option WHERE CURRENT OF <cursor_name> added
DELETE statement has the option WHERE CURRENT OF <cursor_name> added
The cursor_name data is held in the UpdateStmt and DeleteStmt structures and
contains just the name of the cursor.
The pl/pgsql grammar changes in the same manner.
The word CURRENT will be added to the ScanKeywords array in keywords.c.
2.2 Changes to Affected Data Structures
------------------------------------------
The following data structures are affected by this change:
Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt
structures
The Portal will contain a list of structures of relation ids and tuple ids
relating to the tuple held in the QueryDesc structure. There will be one
entry in the relation and tuple id list for each entry in the relation-list
of the statement below:
DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list>
The QueryDesc structure will contain the relation id and the tuple id
relating to the tuple obtained via the FETCH command so that it can be
propagated back to the Portal for storage in the list described above.
The UpdateStmt and DeleteStmt structures have the cursor name added so that
the information is available for use in obtaining the portal structure
related to the cursor previously opened via the DECLARE CURSOR request.
2.3 Changes to the SQL Parser
------------------------------------
At present, although the FOR UPDATE clause of the DECLARE CURSOR command has
been present in the grammar, it causes an error message later in the
processing since cursors are currently not updatable. This now needs to
change. The FOR UPDATE clause has to be valid, but not the FOR SHARE
clause.
The relation names that follow the FOR UPDATE clause will be added to the
rtable in the Query structure and identified by means of the rowMarks array.
In the case of an updatable cursor the FOR SHARE option is not allowed
therefore all entries in the rtable that are identified by the rowMarks array
must relate to tables that are FOR UPDATE.
In the UPDATE or DELETE statements the WHERE CURRENT OF <cursor_name>
clause results in the cursor name being placed in the UpdateStmt or
DeleteStmt structure. During the processing of the functions -
transformDeleteStmt() and transformUpdateStmt() - the cursor name is used to
obtain a pointer to the related Portal structure and the tuple affected by
the current UPDATE or DELETE statement is extracted from the Portal, where it
has been placed as the result of a previous FETCH request. At this point all
the information for the UPDATE or DELETE statement is available so the
statements can be transformed into standard UPDATE or DELETE statements and
sent for re-write/planning/execution as usual.
2.4 Changes to the Optimizer
------------------------------
There is a need to add a TidScan node to planning UPDATE / DELETE statements
where the statements are UPDATE / DELETE at position . This is to enable the
tuple ids of the tuples in the tables relating to the query to be obtained.
There will need to be a new mechanism to achieve this, as at present, a Tid
scan is done only if there is a standard WHERE condition on update or delete
statements to provide Tid qualifier data.
2.5 Changes to the Executor
-------------------------------
There are various options that have been considered for this part of the
enhancement. These are described in the sections below.
We would like to hear opinions on which option is the best way to go or if
none of these is acceptable, any alternate ideas ?
Option 1 MVCC Via Continuous Searching of Database
The Executor is to be changed in the following ways:
1)When the FETCH statement is executed the id of the resulting tuple is
extracted and passed back to the Portal structure to be saved to indicate the
cursor is currently positioned on a tuple.
2)When the UPDATE or DELETE request is executed the tuple id previously
FETCHed is held in the QueryDesc structure so that it can be compared with
the tuple ids returned from the TidScan node processed prior to the actual
UPDATE / DELETE node in the plan. This enables a decision to be made as to
whether the tuple held in the cursor is visible to the UPDATE / DELETE
request according to the rules of concurrency. The result is that, at the
cost of repeatedly searching the database at each UPDATE / DELETE command,
the hash table is no longer required.
This approach has the advantage that there is no hash table held in memory or
on disk so it will not be memory intensive but will be processing intensive.
This is a good one-off solution to the problem and, taken in isolation is
probably the best approach. However, if one considers the method(s) used in
other areas of PostgreSQL, it is probably not the best solution. This option
will probably not be used further.
Option 2 MVCC via New Snapshot
The executor can be changed by adding a new kind of snapshot that is
specifically used for identifying if a given tuple, retrieved from the
database during an update or delete statement should be visible during the
current transaction.
This approach requires a new kind of snapshot (this idea was used by Gavin
for a previous updatable cursor patch but objections were raised.)
Option 3 MVCC Via Hash Table in Memory
The executor can be changed by saving into a hash table and comparing each
tuple in the cursor with that set to check if the tuple should be visible.
This approach has the advantage that it will be quick. It has the
disadvantage that, since the hash table will contain all the tuples of the
table being checked that it may use all local memory for a large table.
Option 4 MVCC Via Hash Table on Disk
When the UPDATE or DELETE request is executed the first time the Tid scan
database retrieval will be done first. At this time the tuple id of each row
in the table to be updated by the request will be available in the executor.
These tuple ids need to be stored in a hash table that is stored to disk, as,
if the table is large there could be a huge number of tuple ids. This data is
then available for comparison with the individual tuple to be updated or
deleted to check if it should be processed. The hash table will exist for the
duration of the transaction, from BEGIN to END (or ABORT).
The hash table is then used to identify if the tuple should be visible during
the current transaction. If the tuple should be visible then the update or
delete proceeds as usual.
This approach has the advantage that it will use little memory but will be
relatively slow as the data has to be accessed from disk.
Option 5 Store Tuple Id in Snapshot.
The Snapshot structure can be changed to include the tuple id. This enables
the current state of the tuple to be identified with respect to the current
transaction.
The tuple id, as identified in the cursor at the point where the
DELETE/UPDATE statement is being processed, can use the snapshot to identify
if the tuple should be visible in the context of the current transaction.
2.6 Changes to the Catalog
----------------------------
The Catalog needs to reflect changes introduced by the updatable cursor
implementation. A boolean attribute is_for_update is to be added to the
pg_cursors implementation. It will define that the cursor is for update
(value is FALSE) or for share (value is TRUE, the default value).
3 Design Assumptions
----------------------------
The following design assumptions are made:
As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples
contained in a cursor can never be updated so these tuples will always appear
in their original form as at the start of the transaction. This is in
breach of the SQL2003 Standard as described in 5WD-02-Foundation-2003-09.pdf,
p 810. The standard requires the updatable cursor to be declared as sensitive.
With respect to nested transactions In PostgreSQL nested transactions are
implemented by defining save points via the keyword SAVEPOINT. A ROLLBACK
TO SAVEPOINT rolls back the database contents to the last savepoint in this
transaction or the begin statement, whichever is closer.
It is assumed that the FETCH statement is used to return only a single row
into the cursor with each command when the cursor is updatable.
According to the SQL2003 Standard Update and Delete statements may contain
only a single base table.
The DECLARE CURSOR statement is supposed to use column level locking, but
PostgreSQL supports only row level locking. The result of this is that the
column list that the standard requires DECLARE <cursor_name> SELECT FOR
UPDATE OF column-list becomes a relation (table) list.
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.
If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au
---------------------------(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
|
| | 17 answers | Add comment |
|
| Free space management within heap page Pavan Deolasee 17:48:33 |
| | I am thinking that maintaining fragmented free space within a heap page
might be a good idea. It would help us to reuse the free space ASAP without
waiting for a vacuum run on the page. This in turn will lead to lesser heap
bloats and also increase the probability of placing updated tuple in the
same heap page as the original one.
So during a sequential or index scan, if a tuple is found to be dead, the
corresponding line pointer is marked "unused" and the space is returned to a
free list. This free list is maintained within the page. A linked-list can
be used for this purpose and the special area of the heap-page can be used
to track the fragment list. We can maintain some additional information
about the fragmented space such as, total_free_space, max_fragment_size,
num_of_fragments etc in the special area.
During UPDATEs, if we find that there is no free space in the block, the
fragment list is searched (either first-fit or best-fit), the required space
is consumed and the remaining space is returned to the free list.
We might not be able to reuse the line pointers because indexes may have
references to it. All such line pointers will be freed when the page is
vacuumed during the regular vacuum.
Thanks,
Pavan
EnterpriseDB http://www.enterprisedb.com
|
| | 18 answers | Add comment |
|
| Compiling on JDK 6 Frank Spies 17:23:29 |
| | Hi all,
i tried to compile the jdbc driver on jdk 6. That did not work, several
methods are not implemented. Is there a roadmap when this will be
implemented? It was quite easy to make the code compile, by just
throwing exceptions from all unimplemented methods. Shouldn't we do that
to at least have the possibility to compile under jdk 6?
Thanks, Frank
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Searching some sites explaing about PosgtreSQL source codes Re-Plore 15:49:45 |
| | Hi, I am now reading PostgreSQL source codes, but i am not familiar to this
codes.
So i am now seraching some sites which explaing about PostgreSQL source
codes, or it's structure.
If you know a good site explaing PostgreSQL's source codes.
Please teach me.
Thanks a lot of your conservation!
|
| | 1 answer | Add comment |
|
| Applet Connectivity - PLEASE help Marc 14:51:15 |
| | OK, I'll say right up front I'm a postgres novice at best.
I've spent quite some time researching this tonight and trying out a few
things to no avail.
The basic question is can an applet connect to a postgres database and
if so how (I need specific details)?
Postgres, the database, the web server and signed applet are all on the
same machine.
I'm using Postgres 8.2 and Java 1.5 w/ the postgresql-8.2-504.jdbc3 jdbc
driver.
The software works when run through my IDE (Eclipse) but not as an
applet in a browser.
I've set listen_addresses = '*' in postgresql.conf and my pg_hba
settings are:
local all all md5
host all all 127.0.0.1/32 trust.
Here's the code I'm using to try to make the connection where
strServer = :jdbc ostgresql://localhost/Arco
strDriver = org.postgresql.Driver
strUser = postgres
strPswd = fred
public DBConnection(Trace trace, JApplet p_applet)
throws Exception
{
URL dbIniURL;
URLConnection urlConn;
BufferedReader in;
int vals = 0;
String nextVal;
String strProp;
String strVal;
String strUser = "";
String strPswd = "";
int pos;
Properties props = new Properties();
try
{
System.out.println("Instantiate DBConnection.");
dbIniURL = new URL(p_applet.getDocumentBase(), "db.ini");
urlConn = dbIniURL.openConnection();
in = new BufferedReader(new
InputStreamReader(urlConn.getInputStream()));
while (vals < 4)
{
nextVal = in.readLine();
vals = vals + 1;
System.out.println("db.ini: " + nextVal.trim());
pos = nextVal.indexOf(":");
if (pos>0)
{
strProp = nextVal.substring(0,pos);
strVal = nextVal.substring(pos+1);
if (strProp.compareToIgnoreCase("Server") == 0)
{
strServer = strVal;
}
else if (strProp.compareToIgnoreCase("Driver") == 0)
{
strDriver = strVal;
}
else if (strProp.compareToIgnoreCase("User") == 0)
{
strUser = strVal;
}
else if (strProp.compareToIgnoreCase("Password") == 0)
{
strPswd = strVal;
}
}
}
drv = (Driver)Class.forName(strDriver).newInstance();
DriverManager.registerDriver(drv);
props.setProperty("user",strUser.trim());
props.setProperty("password",strPswd.trim());
System.out.println("Attempting to connecting to postgres
db...");
dbConn= DriverManager.getConnection(strServer, props);
System.out.println("Connected to postgres db.");
}
catch (IOException ioe)
{
System.out.println("Error trying to connect to postgres db:");
ioe.printStackTrace();
throw new Exception(ioe.getMessage());
}
}
The error I get in the console is:
Java Plug-in 1.6.0
Using JRE version 1.6.0 Java HotSpot(TM) Client VM
User home directory = C:\Documents and Settings\Marc
----------------------------------------------------
c: clear console window
f: finalize objects on finalization queue
g: garbage collect
h: display this help message
l: dump classloader list
m: print memory usage
o: trigger logging
p: reload proxy configuration
q: hide console
r: reload policy configuration
s: dump system and deployment properties
t: dump thread list
v: dump thread stack
x: clear classloader cache
0-5: set trace level to <n>
----------------------------------------------------
Init applet.
Call new DBConnection.
Instantiate DBConnection.
db.ini: Server:jdbcostgresql://localhost/Arco
db.ini: Driver:org.postgresql.Driver
Attempting to connecting to postgres db...
org.postgresql.util.PSQLException: Something unusual has occured to
cause the driver to fail. Please report this exception.
at org.postgresql.Driver.connect(Driver.java:276)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at db.DBConnection.<init>(DBConnection.java:169)
at ui.BaseApplet.init(BaseApplet.java:138)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.security.AccessControlException: access denied
(java.net.SocketPermission 127.0.0.1:5432 connect,resolve)
at java.security.AccessControlContext.checkPermission(Unknown Source)
at java.security.AccessController.checkPermission(Unknown Source)
at java.lang.SecurityManager.checkPermission(Unknown Source)
at java.lang.SecurityManager.checkConnect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at org.postgresql.core.PGStream.<init>(PGStream.java:59)
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:77)
at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:382)
at org.postgresql.Driver.connect(Driver.java:260)
... 6 more
I think that covers it. I'm pretty wiped out being as I've been working
on this for about 4 hrs now.
Your help is really appreciated!
Thanks,
Marc
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 3 answer | Add comment |
|
| Who is Slony Master/Slave + general questions. Guest 14:50:10 |
| | Hello,
I'm starting to use slony as a redundancy solution for the project
I'm currently working on. Running SuSE Linux 9 where one machine
contains the prime database and the second machine contains the backup
database. The Slony version I'm using is 1.1.2. If some of the issues
have been addressed in the newer version of Slony, please let me know.
I have looked at the Nagios scripts and others and am still left with
questions regarding how to dynamically determine who is slave and who
is master during normal and failover operations. Take a scenario that
you want to check the state of the system without prior knowledge of
the node setup, how would you determine which machine is the prime and
which one is the slave?
Also I'm having issues with the slonik script (below) that is supposed
to handle the failover to the slave in case of master failure. For
some reason it hangs and I was wondering if there are known issues with
it. The test condition I'm working with is: reboot the master, the
slave is supposed to take over.
slonik <<_EOF_
# ----
# This defines which namespace the replication system uses
# ----
cluster name = $CLUSTER;
# ----
# Admin conninfo's are used by the slonik program to connect
# to the node databases. So these are the PQconnectdb arguments
# that connect from the administrators workstation (where
# slonik is executed).
# ----
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 port=5432
user=$SLONY_USER1';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2
user=$SLONY_USER2';
# ----
# Node 2 subscribes set 1
# ----
failover ( id = 1, backup node = 2);
_EOF_
Thanks a lot for your help,
Slawek
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 6 answers | Add comment |
|
| Conferences/UGs in March? Josh Berkus 14:22:57 |
| | All,
I need to take a trip to Norway in March. I'd like to stop off at an Open
Source conference either on the way there or the way back. Anything in
Northern/Western Europe in March? Or should I stop off in London or
Paris just to visit the community there?
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 2 answer | Add comment |
|
|
