|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Saturday, 27 January 2007
|
| Re: BUG #2917: spi_prepare doesn't accept typename aliases Andrew Dunstan 05:48:22 |
| | I wrote:>
I see that SQL level prepare calls regprocin() to resolve type names, > so maybe we should that for the PLs when calling SPI_prepare as well.
Of course, that should be regtypein()
cheers
andrew
---------------------------(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
|
| |  8 answers | Add comment |
|
| Re: Searching some sites explaing about PosgtreSQL Bruce Momjian 05:39:36 |
| |
Can we add a link to the doxygen web site from our site:
http://www.postgresql.org/developer/coding
link to:
http://www.mcknight.de/pgsql-doxygen/cvshead/html/
It seems like a useful resource.
---------------------------------------------------------------------------
=?ISO-8859-1?Q?Luis_D._Garc=EDa?= wrote:> Hi, I'm working with Postgres Source Code too, and there's a site that could> be helpfull> for you as it has been for me.>
Greetings...> 2007/1/24, re-plore <gotutotukotu@gmail.com>:> > 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!> > -- > Luis D. Garc?a M.> Telf: (+58) 2418662663> Cel.: (+58) 4143482018> - FACYT - UC -> - Computaci?n -
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | Add comment |
|
| No ~ operator for box, point Jim C. Nasby 05:32:11 |
| | decibel=# select version();
PostgreSQL 8.3devel on i386-apple-darwin8.8.2, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363)
decibel=# select box '((0,0),(2,2))' ~ point '(1,1)';
ERROR: operator does not exist: box ~ point
LINE 1: select box '((0,0),(2,2))' ~ point '(1,1)';
Any reason this doesn't exist?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 5 answers | Add comment |
|
| Piggybacking vacuum I/O Heikki Linnakangas 05:29:57 |
| | I've been looking at the way we do vacuums.
The fundamental performance issue is that a vacuum generates
nheapblocks+nindexblocks+ndirtyblocks I/Os. Vacuum cost delay helps to
spread the cost like part payment, but the total is the same. In an I/O
bound system, the extra I/O directly leads to less throughput.
Therefore, we need to do less I/O. Dead space map helps by allowing us
to skip blocks that don't need vacuuming, reducing the # of I/Os to
2*ndirtyblocks+nindexblocks. That's great, but it doesn't help us if the
dead tuples are spread uniformly.
If we could piggyback the vacuum I/Os to the I/Os that we're doing
anyway, vacuum wouldn't ideally have to issue any I/O of its own. I've
tried to figure out a way to do that.
Vacuum is done in 3 phases:
1. Scan heap
2. Vacuum index
3. Vacuum heap
Instead of doing a sequential scan, we could perform the 1st phase by
watching the buffer pool, scanning blocks for dead tuples when they're
in memory and keeping track of which pages we've seen. When all pages
have been seen, the tid list is sorted and 1st phase is done.
In theory, the index vacuum could also be done that way, but let's
assume for now that indexes would be scanned like they are currently.
The 3rd phase can be performed similarly to the 1st phase. Whenever a
page enters the buffer pool, we check the tid list and remove any
matching tuples from the page. When the list is empty, vacuum is complete.
Of course, there's some issues in the design as described above. For
example, the vacuum might take a long time if there's cold spots in the
table. In fact, a block full of dead tuples might never be visited again.
A variation of the scheme would be to keep scanning pages that are in
cache, until the tid list reaches a predefined size, instead of keeping
track of which pages have already been seen. That would deal better with
tables with hot and cold spots, but it couldn't advance the relfrozenid
because there would be no guarantee that all pages are visited. Also, we
could start 1st phase of the next vacuum, while we're still in the 3rd
phase of previous one.
Also, after we've seen 95% of the pages or a timeout expires, we could
fetch the rest of them with random I/O to let the vacuum finish.
I'm not sure how exactly this would be implemented. Perhaps bgwriter or
autovacuum would do it, or a new background process. Presumably the
process would need access to relcache.
One issue is that if we're trying to vacuum every table simultaneously
this way, we'll need more overall memory for the tid lists. I'm hoping
there's a way to implement this without requiring shared memory for the
tid lists, that would make the memory management a nightmare. Also, we'd
need changes to bufmgr API to support this.
This would work nicely with the DSM. The list of pages that need to be
visited in phase 1 could be initialized from the DSM, largely avoiding
the problem with cold spots.
Any thoughts before I start experimenting?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 27 answers | Add comment |
|
| 14.4 If You Are Upgrading - Suggested Improvements Thomas F. O'Connell 04:27:37 |
| | I've done a number of upgrades of postgres from one minor release to
another, and unless I'm missing something, there's nothing anywhere
in section 14 that provides documentation on how to do this.
Generally, it's as simple as:
configure
make
make install
pg_ctl stop [or equivalent, if stop scripts exist]
pg_ctl start -D <data directory> [or equivalent, if start scripts exist]
But it seems like it would be helpful for administrators to break
down 14.4 into two sections:
14.4.1 - If You Are Upgrading a Minor Release
14.4.2 - If You Are Upgrading a Major Release (or a Release Requiring
an initdb)
Just a suggestion. If this strikes others as a good idea, I'd be
happy to draft something.
--
Thomas F. O'Connell
optimizing modern web applications
: for search engines, for usability, and for performance :
http://o.ptimized.com/
615-260-0005
|
| | 7 answers | Add comment |
|
| Loop plpgsql recordset Furesz Peter 00:42:26 |
| | Hello,
How can I loop a PL/PgSQL recorset variable? The example:
DECLARE
v_tmp_regi RECORD;
v_tmp RECORD;
BEGIN
SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE
sf.termekfajta_id=
a_termekfajta_id AND sf.marka_id=a_marka_id;
DELETE FROM sulyozas_futamido;
FOR v_tmp IN v_tmp_regi LOOP
--I would like to work here with the old recordset!
END LOOP;
^^^^^^^^^^^^^^
-- This is not working !!!
END;
|
| | 1 answer | Add comment |
|
| Re: Speaking of upgrades... Douglas McNaught 00:42:26 |
| | Ron Johnson <ron.l.johnson@cox.net> writes:
How much does the on-disk structure of *existing* tables and indexes> change between x.y versions?> Between, for example, 8.0 and 8.2?
Enough to require a dump/reload in order to upgrade. 
Within major versions (8.2.0, 8.2.1, etc) the files are compatible so
you can do in-place upgrades (except in extraordinary circumstances).
-Doug
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | Add comment |
|
| Ayuda sobre Indices Julio Caicedo 00:42:26 |
| | Buen Dia.
Por favor si saben como, me gustaria saber como puedo eliminar un indice
PERO SOLO si este existe. Como valido si existe o no el indice para luego
eliminarlo ??
Gracias.
-----------
Please, i need drop index but ONLY and ONLY this exist index. Thanks.
---------------------------(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
|
| | 1 answer | Add comment |
|
| Windows 2K Installation difficulties... Neil Bibbins 00:42:26 |
| | Hello,
I'm having difficulty installing PostgreSQL 8.2 on Windows 2000. It gets
most of the way through the installation and fails (I think) after trying to
initialize the database. The log message is:
The database cluster will be initialized with locale C.
fixing permissions on existing directory C:/Program
Files/PostgreSQL/8.2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers/max_fsm_pages ... 400kB/20000
creating configuration files ... ok
creating template1 database in C:/Program Files/PostgreSQL/8.2/data/base/1
... Bad command or file name
could not write to child process: Invalid argument
Initdb: removing contents of data directory "C:/Program
Files/PostgreSQL/8.2.data
I have removed all virus software, although I hope to reinstall it after
installation. (Can PostgresSQL really not run as an application with virus
protection installed? Ouch...)
I have tried deselecting the initialization box and initializing afterward,
but this also doesn't work. Possibly I'm using the wrong command, which is
one that I found from a post from Magnus several years ago.
I have installed Cygwin, but this also didn't help.
Can anyone assist? I've done many forum searches but nothing seems to work.
I don't think I'm attempting the impossible!
Thanks for any insight. Much appreciated.
|
| | 2 answer | Add comment |
|
| PostgreSQL data loss BluDes 00:42:26 |
| | Hi everyone,
I have a problem with one of my costomers.
I made a program that uses a PostgreSQL (win32) database to save its data.
My customer claims that he lost lots of data reguarding his own clients
and that those data had surely been saved on the database.
My first guess is that he is the one who deleted the data but wants to
blame someone else, obviously I can't prove it.
Could it be possible for PostgreSQL to lose its data? Maybe with a file
corruption? Could it be possible to restore these data?
My program does not modify or delete data since its more like a log that
only adds information. It is obviously possible to delete these logs but
it requires to answer "yes" to 2 different warnings, so the data can't
be deleted accidentally.
I have other customers with even 10 times the amount of data of the one
who claimed the loss but no problems with them.
He obviously made no backups (and claims whe never told him to do them
so we are responsible even for this) though the program has a dedicated
Backup-section.
Any suggestion?
Daniele
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Function returning SETOF using plpythonu Lu s Sousa 00:42:26 |
| | Hi,
Is it possible to return rows from a function written in plpythonu using
SETOF?
Example:
CREATE FUNCTION "test_python_setof"()
RETURNS SETOF text AS '
records=plpy.execute("SELECT name FROM interface");
return records
' LANGUAGE 'plpythonu';
With this code is returning the object from the execution:
<PLyResult object at 0xb703e458>
Best regards,
Lu s Sousa
---------------------------(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
|
| | 1 answer | Add comment |
|
| Re: [Fwd: [PORTS] M$ SQL server DTS package equivalent Paul Lambert 00:42:26 |
| | Thanks all for your tips and pointers.
Looking at copy I think it may do just what I need. The tables I load
the data into have the same columns in the same order as those in the
CSV file. Loading data in this manner is going to be a rare occurance -
just when we install a new customer site and need to do an initial
transfer of data from the main system before we switch on my real-time
replication program. The programs that extract these csv files already
take care of duplicate key checking and so forth, so there shouldn't be
any issues as far as data integrity checking goes. I.e. there's no
actual data transformation, row merging and the like.
Thanks again to everyone who's offered some advice, much appreciated.
Regards,
Paul.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | Add comment |
|
| Proposal: Snapshot cloning Jan Wieck 00:42:26 |
| | Granted this one has a few open ends so far and I'd like to receive some
constructive input on how to actually implement it.
The idea is to clone an existing serializable transactions snapshot
visibility information from one backend to another. The semantics would
be like this:
backend1: start transaction;
backend1: set transaction isolation level serializable;
backend1: select pg_backend_pid();
backend1: select publish_snapshot(); -- will block
backend2: start transaction;
backend2: set transaction isolation level serializable;
backend2: select clone_snapshot(<pid>); -- will unblock backend1
backend1: select publish_snapshot();
backend3: start transaction;
backend3: set transaction isolation level serializable;
backend3: select clone_snapshot(<pid>);
...
This will allow a number of separate backends to assume the same MVCC
visibility, so that they can query independent but the overall result
will be according to one consistent snapshot of the database.
What I try to accomplish with this is to widen a bottleneck, many
current Slony users are facing. The initial copy of a database is
currently limited to one single reader to copy a snapshot of the data
provider. With the above functionality, several tables could be copied
in parallel by different client threads, feeding separate backends on
the receiving side at the same time.
The feature could also be used by a parallel version of pg_dump as well
as data mining tools.
The cloning process needs to make sure that the clone_snapshot() call is
made from the same DB user in the same database as corresponding
publish_snapshot() call was done. Since publish_snapshot() only
publishes the information, it gained legally and that is visible in the
PGPROC shared memory (xmin, xmax being the crucial part here), there is
no risk of creating a snapshot for which data might have been removed by
vacuum already.
What I am not sure about yet is what IPC method would best suit the
transfer of the arbitrarily sized xip vector. Ideas?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 17 answers | Add comment |
|
| Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled Jan Wieck 00:40:12 |
| | The experience with Slony-I has shown that
a) different behavior of triggers and rules on a transactions origin
and a replica is essential;
b) mucking around with the system catalog to achieve this is futile.
This would be even more catastrophic in a multimaster environment, where
regular transaction origin and replica behavior are required on a per
session level concurrently.
To achieve the required flexibility, we need to change the definition of
the pg_trigger attribute tg_enabled. It currently is a boolean. I would
like to change it into a char along with the syntax of ALTER TRIGGER.
The value definitions of tg_enabled would be
A fires always
N fires never
O fires on transaction origin only
R fires on replica only
Anyone preferences how to map that to ALTER TRIGGER?
A new per session GUC variable, restricted to superusers, will define if
the session is in origin or replica mode.
Likewise the system catalog pg_rewrite is extended with an attribute
ev_enabled. It will have the same possible values and a new command,
ALTER RULE, will match the functionality of ALTER TRIGGER.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.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
|
| | 3 answer | Add comment |
|
| pg_restore exclude schema from being droped option Kostis Mentzelos 00:18:44 |
| | Hi list,
I am using pg_dump and pg_restore to backup and restore a database but
there is something
that I believe is missing from the restore process:
an option in pg_restore to exclude a schema from being dropped when -c
option is defined.
And here is why:
Suppose that I have a database with about 12 tables of customer data
(address, notes, configuration ... no more than 10.000 rows each) and 50
tables of history data (history files with about 1.000.000 rows each).
Now, to backup the database I choose to create 2 scripts, BackupData.sh
to backup all small tables and BackupHist.sh to backup history tables.
When I call pg_restore -c to restore data tables, pg_restore report a
failure because it is trying to drop a schema that it is not empty. So
it would be very helpful to have an option to exclude the schema (for
example: public) from being dropped.
I now that I there are some alternatives for example: pg_restore -l,
comment out the drop schema line and pg_restore -L or put data tables
and hist tables into separate schemas but an option to pg_restore would
be a lot easier, I guess.
What do you thing?
regards,
Kostis Mentzelos
---------------------------(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
|
| | 1 answer | Add comment |
|
| VC2005 build and pthreads Gevik Babakhani 00:10:20 |
| | Folks,
I would like to build pg on VC2005. How do I use pthreads that is
mentioned in the README file. Do I need the DLL? Sources? LIB?
Where do I install or copy them..
Regards,
Gevik
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Re: [PERFORM] how to plan for vacuum? Alvaro Herrera 00:07:45 |
| | Jim C. Nasby wrote:
I'll generally start with a cost delay of 20ms and adjust based on IO> utilization.
I've been considering set a default autovacuum cost delay to 10ms; does
this sound reasonable?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 6 answers | Add comment |
Friday, 26 January 2007
|
| Can you specify the pg_xlog location from a config file? Karen Hill 23:50:09 |
| | Windows doesn't support symlinks. Is it possible instead for there to
be a config file that lets one set where the pg_xlog directory will sit?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 4 answer | Add comment |
|
| NULL value in subselect in UNION causes error Jan Wieck 23:46:50 |
| | Checked it against HEAD and 8.2:
postgres=# select 1, 1, 1 union select * from (select 2, null, 2) two;
ERROR: failed to find conversion function from "unknown" to integer
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 2 answer | Add comment |
|
| Problems with MFC AddNew Guest 23:29:11 |
| | Hello,
I'm having some difficulty with adding new records using the ODBC driver
with MFC. This is only occurring is certain situations...
I created a simple table:
CREATE TABLE project (project_id INT PRIMARY KEY);
Using Visual Studio C++ I created a CRecordset class (CProject) to
exchange data with the project table. I then execute the following code:
CDatabase database;
database.OpenEx(_T("DSN=PostgreSQL30"));
CProject project(&database);
project.Open();
project.AddNew();
project.m_project_id = 1;
project.Update();
I get an Access Violation on the Update(). The log file shows the
following error:
psqlodbc_3100.log:
conn=01104290, query='SELECT "project_id" , "ctid", "project_id" FROM
"public"."project"'
[ fetched 0 rows ]
mylog_3100.log:
[1800-4.186]extend_putdata_info: entering ... self=016C5EC4,
parameters_allocated=0, num_params=1
[1800-4.191]extend_putdata_info: unable to create 1 new pdata from 0 old
pdata
Now I've been able to get the Update() to work in the following situations:
- remove the primary key:
CREATE TABLE project (project_id INT);
- change the int to bigint:
CREATE TABLE project (project_id BIGINT PRIMARY KEY);
- make sure at least one record exists in the table:
CREATE TABLE project (project_id INT PRIMARY KEY);
INSERT INTO project (project_id) VALUES (2);
So what's going on? I would like to use int, but if I have to I can
switch to bigint. Thanks for your help.
Ian
---------------------------(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
|
| | 1 answer | Add comment |
|
| Query regarding PostgreSQL date/time binary format for libpq Shoaib Naazir 23:21:21 |
| | Hi, I am working on an application using PostgreSQL. I need to send date to PostgreSQL in binary format using the libpq interface. I have walked through the PostgreSQL code base, did find some reference to the date/time binary format but didn't get the exact format. To much code to digest in one parse . Can you please provide me with any good reference to the exact binary format dbserver expect for date/time. I just can't use the text format  . Any reference material, paper, web page or a mail specifying the exact binary date/time format will do. Thanks in advance. I really appreciate your time. regards,Shoaib Naazir.
Artificial intelligence is no match for natural stupidity.
_________________________________________________________________
Get into the holiday spirit, chat with Santa on Messenger.
http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us |
| | 1 answer | Add comment |
|
|
