Re: [GENERAL] does postgresql works on distributed systems?
Roberts, Jon wrote: He's talking about having the raw database files on a file server (eg SMB share). DB's like firebird and sqlite can handle this way of accessing the data using the embedded engines. Active-active, multiple server databases are either a shared nothing or a shared disk system. Oracle, for instance is a shared disk system where multiple database instances can connect to the same underlying disk. I'm not sure the point you are making. We have all our Oracle databases stored on a NetApp, so I think this is the kind of configuration you are discussing. However, each Oracle instance on a single server completely owns the files on the NetApp related to that instance. All Oracle instances on all servers share the same NetApp, but that's because it's just a big file server. In the event of a DB server failure, we can bring up the same instance on a backup DB server, but then *it* completely owns all files related to that instance. Only one instance can be accessing the files related to that instance at any point in time. The same could be done with PostgreSQL. As I said, the NetApp is just a fileserver. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Failing to recover after panic shutdown
Yes, we are copying from pg_xlog. By doing so we let the WAL-segments fill up (not using timeout) and we are able to recover within a 10 minute interval. Could it be that this copy operation is causing the problem? Per -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: 3. juni 2008 15:47 To: Per Lauvås Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Failing to recover after panic shutdown Per Lauvås wrote: > Hi > > I am running Postgres 8.2 on Windows 2003 server SP2. > > Every now and then (2-3 times a year) our Postgres service is down > and we need to manually start it. This is what we find: > > In log when going down: > 2008-06-02 13:40:02 PANIC: could not open file > "pg_xlog/0001001C0081" (log file 28, segment 129): > Invalid argument Are you by any chance running an antivirus or other "security software" on this server? > We are archiving WAL-segments at a remote machine, and we are copying > non-filled WAL-segments every 10 minutes to be able to rebuild the DB > with a maximum of 10 minutes of missing data. (I don't know if that > has anything to do with it). How are you copying these files? Are you saying you're actually copying the files out of the pg_xlog directory, or are you using the archive_command along with archive_timeout? //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
On Tue, 3 Jun 2008, "Roberts, Jon" <[EMAIL PROTECTED]> writes: > PostgreSQL does not have either a shared disk or shared nothing > architecture. But there are some turn arounds for these obstacles: - Using pgpool[1], sequoia[2], or similar tools[3] you can simulate a "shared nothing" architecture. - Using an SSI (Single System Image) framework (e.g. OpenSSI[4]), you can build your own "shared disk" architecture for any application. I'm planning to make a survey regarding PostgreSQL performance on OpenSSI. There are some obstacles mostly caused by shared-memory architecture of PostgreSQL, but that claim is -- AFAIK -- totally theoratical. There aren't any benchmarks done yet that explains shared-memory bottlenecks of PostgreSQL on an OpenSSI framework. If anybody have experience with PostgreSQL on OpenSSI, I'll be happy to hear them. (Yeah, there were some related posts in the past; but they were mostly noise.) Regards. [1] http://pgpool.projects.postgresql.org/ [2] http://sequoia.continuent.org/ [3] http://www.postgresql.org/docs/8.3/interactive/high-availability.html [4] http://wiki.openssi.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generate SQL Statements
On Tuesday 03 June 2008 20:10, Steve Crawford wrote: > Terry Lee Tucker wrote: > > Greetings: > > > > I was wondering if anyone knows of a third party product that will > > generate SQL statements for creating existing tables. We have to provide > > table definition statements for out parent company. Any ideas? > > Why 3rd party? How about: > > pg_dump --schema-only -t table_name... ? > > Alternately, roll-your-own using the system tables. A good place to > start is by running psql with the --echo-queries option to see the > queries it runs "behind the scenes". You can read the queries for things > like "\d+ tablename" then modify them to suit. > > Cheers, > Steve Now why didn't I think of that :o/ Thanks for the help... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] How to just "link" to some data feed
Scott Marlowe wrote: > On Tue, Jun 3, 2008 at 9:58 PM, Albretch Mueller > <[EMAIL PROTECTED]> wrote: > > On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett > <[EMAIL PROTECTED]> wrote: > >> That's essentially the same as the COPY you quoted in your > original email, > >> isn't it? So.. what exactly is it you want to do that COPY > doesn't do? > > ~ > > well, actually, not exactly; based on: > > ~ > > http://postgresql.com.cn/docs/8.3/static/sql-copy.html > > ~ > > COPY [FROM|TO] > > ~ > > import/export the data into/out of PG, so you will be essentially > > duplicating the data and having to synch it. This is > exactly what I am > > trying to avoid, I would like for PG to handle the data > right from the > > data feed > > I think what you're looking for is the equivalent to oracles external > tables which invoke sqlldr every time you access them in the > background. No such animal in the pg universe that I know of. There was a similar discussion of this on -hackers in April. Closest to this idea was http://archives.postgresql.org/pgsql-hackers/2008-04/msg00250.php Regards, Stephen Denne. -- At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] How to just "link" to some data feed
On Tue, Jun 3, 2008 at 9:58 PM, Albretch Mueller <[EMAIL PROTECTED]> wrote: > On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <[EMAIL PROTECTED]> wrote: >> That's essentially the same as the COPY you quoted in your original email, >> isn't it? So.. what exactly is it you want to do that COPY doesn't do? > ~ > well, actually, not exactly; based on: > ~ > http://postgresql.com.cn/docs/8.3/static/sql-copy.html > ~ > COPY [FROM|TO] > ~ > import/export the data into/out of PG, so you will be essentially > duplicating the data and having to synch it. This is exactly what I am > trying to avoid, I would like for PG to handle the data right from the > data feed I think what you're looking for is the equivalent to oracles external tables which invoke sqlldr every time you access them in the background. No such animal in the pg universe that I know of. > Well, no, but I was hoping to get an answer here because I mostly > access PG through jdbc and also because java developer would generally > be more inclined to these types of DB-independent data formats, > reusing, transferring issues Removed pgsql-jdbc from cc list. You're still better off sending to the right list. And so are we. The general list has a much larger readership than jdbc, and it's far more likely you'll run into someone with oracle experience here who knows about the external table format, etc... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] How to just "link" to some data feed
Albretch Mueller wrote: On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <[EMAIL PROTECTED]> wrote: That's essentially the same as the COPY you quoted in your original email, isn't it? So.. what exactly is it you want to do that COPY doesn't do? ~ well, actually, not exactly; based on: ~ http://postgresql.com.cn/docs/8.3/static/sql-copy.html ~ COPY [FROM|TO] ~ import/export the data into/out of PG, so you will be essentially duplicating the data and having to synch it. This is exactly what I am trying to avoid, I would like for PG to handle the data right from the data feed As Dave said, PG won't magically keep the data up to date for you, you will need some external process to do the synchronization with the feed. That could use COPY if it wanted .. Then you said: Hmm! Doesn't PG have a way to do something like this, say in MySQL: load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, uniqComments) and even in low end (not real) DBs like MS Access? But isn't this doing exactly what PG's COPY does - loads data, once, from a local file, with no ongoing synchronization? Is there a technical reason for that, or should I apply for a RFE? Personally I don't see this sort of synchronization as something that you want the core DB to be doing anyway. The rules for how you get the data, how often you check for updates, how you merge the updates, and so on are very application specific. -O -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] How to just "link" to some data feed
On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <[EMAIL PROTECTED]> wrote: > That's essentially the same as the COPY you quoted in your original email, > isn't it? So.. what exactly is it you want to do that COPY doesn't do? ~ well, actually, not exactly; based on: ~ http://postgresql.com.cn/docs/8.3/static/sql-copy.html ~ COPY [FROM|TO] ~ import/export the data into/out of PG, so you will be essentially duplicating the data and having to synch it. This is exactly what I am trying to avoid, I would like for PG to handle the data right from the data feed ~ > Anyway, it's not really JDBC related. ~ Well, no, but I was hoping to get an answer here because I mostly access PG through jdbc and also because java developer would generally be more inclined to these types of DB-independent data formats, reusing, transferring issues ~ lbrtchx -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
> Justin wrote: > > > > > > aravind chandu wrote: > > Hi, > > >> My question is > > >>Microsoft sql server 2005 cannot be shared on multiple systems > > i,e in a network environment when it is installed in one system it > > cannot be accessed one other systems. > > > > > > This don't make any sense. Are your taking about sharing the actual > > mdb files or access the service itself??? This question is just > > confusing. > > > > > > Your Questions are confusing can you clarify > > I'm guessing at what you mean??? > He's talking about having the raw database files on a file server (eg > SMB share). DB's like firebird and sqlite can handle this way of > accessing the data using the embedded engines. Active-active, multiple server databases are either a shared nothing or a shared disk system. Oracle, for instance is a shared disk system where multiple database instances can connect to the same underlying disk. Greenplum, Teradata, and Netezza are examples of shared nothing systems. http://en.wikipedia.org/wiki/Shared_nothing_architecture http://en.wikipedia.org/wiki/Shared_disk_file_system PostgreSQL does not have either a shared disk or shared nothing architecture. It is similar to SQL Server where replication and/or failover is how you can get high availability. http://www.postgresql.org/docs/8.3/interactive/high-availability.html Greenplum and EnterpriseDB are both based on PostgreSQL and use a shared nothing architecture to achieve and active-active system. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
Justin wrote: aravind chandu wrote: Hi, >> My question is >>Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems. This don't make any sense. Are your taking about sharing the actual mdb files or access the service itself??? This question is just confusing. Your Questions are confusing can you clarify I'm guessing at what you mean??? He's talking about having the raw database files on a file server (eg SMB share). DB's like firebird and sqlite can handle this way of accessing the data using the embedded engines. Aravind - read http://msdn.microsoft.com/en-us/library/ms190611.aspx and it should help you understand how to database servers (including postgres) work in network environments. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generate SQL Statements
Terry Lee Tucker wrote: Greetings: I was wondering if anyone knows of a third party product that will generate SQL statements for creating existing tables. We have to provide table definition statements for out parent company. Any ideas? Why 3rd party? How about: pg_dump --schema-only -t table_name... ? Alternately, roll-your-own using the system tables. A good place to start is by running psql with the --echo-queries option to see the queries it runs "behind the scenes". You can read the queries for things like "\d+ tablename" then modify them to suit. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Generate SQL Statements
Greetings: I was wondering if anyone knows of a third party product that will generate SQL statements for creating existing tables. We have to provide table definition statements for out parent company. Any ideas? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange statistics
Henrik wrote:
Hi list,
I'm having a table with a lots of file names in it. (Aprox 3 million) in
a 8.3.1 db.
Doing this simple query shows that the statistics is way of but I can
get them right even when I raise the statistics to 1000.
db=# alter table tbl_file alter file_name set statistics 1000;
ALTER TABLE
db=# analyze tbl_file;
ANALYZE
db=# explain analyze select * from tbl_file where lower(file_name) like
lower('to%');
QUERY PLAN
Bitmap Heap Scan on tbl_file (cost=23.18..2325.13 rows=625 width=134)
(actual time=7.938..82.386 rows=17553 loops=1)
Filter: (lower((file_name)::text) ~~ 'to%'::text)
-> Bitmap Index Scan on tbl_file_idx (cost=0.00..23.02 rows=625
width=0) (actual time=6.408..6.408 rows=17553 loops=1)
Index Cond: ((lower((file_name)::text) ~>=~ 'to'::text) AND
(lower((file_name)::text) ~<~ 'tp'::text))
Total runtime: 86.230 ms
(5 rows)
How can it be off by a magnitude of 28??
These are statistics and represent an only estimate! In this case, the
planner seems to be doing the right thing(tm) anyway.
Statistics is a frequently misunderstood subject and usually provides
excellent material to draw plain wrong conclusions. There is a good
chance that due to the physical layout of your data, the algorithms in
the statistics collector, the existence of uncertainty and some more
unknown factors your statistics will be biased. This is a situations
where you noticed it.
Running "SELECT * FROM pg_stats;" will give you the statistics the
planner uses and can provide some hints to why the planner has chosen
these estimates.
Probably statistics will vary between ANALYZE runs. Its also possible to
try "CLUSTER" and friends. Try different queries and look at the deviations.
All in all, you should really start worrying when the planner starts
planning inefficient queries. Since its a filename, it might be highly
irregular (random) and a low statistics target might be good enough anyways.
Unfortunately I'm not a statistics expert...
- Joris
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
aravind chandu wrote: Hi, >> My question is >>Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems. This don't make any sense. Are your taking about sharing the actual mdb files or access the service itself??? This question is just confusing. I have SQL Server 2000 that runs our website and other older applications. we are moving off of it but we have lots of people using the services via ADO.Net, ODBC, OLE-DB and COM. There are all kinds of API interfaces to pick from to get to SQL server. Are you talking license limitations you are running into? There are big restrictions in that front. SQL Server Express is hard coded on its limitation on the number users it can sever at any given point. Now MS SQL Server Standard/Enterprise can be purchased in a couple of different client license modes and i'm way behind on what the current configuration options are. >>One can access only from a system where it is already installed but not on the system where there is no sqlserver.Is postgresql similar to sql server or does it supports >>network sharing i,e one one can access postgresql from any system irrespective on which system it is installed. Again What are you talking about? The actual files or access to the Service/Port??? Your Questions are confusing can you clarify I'm guessing at what you mean???
Re: [GENERAL] does postgresql works on distributed systems?
Excuse me, but maybe I'm misunderstanding your statements and questions here? MS SQL Server most certainly 'can be' accessed from a network, three ways immediately come to mind: - isql command line - osql command line - PERL using DBI interface ODBC Drivers help in some configuration scenarios, but there is no question that MS SQL Server can be accessed from any network configuration, suffice it to say there is no security mechanism denying this access. On your second point, postgresql, absolutely can be accessed as well over the network! On Tue, 3 Jun 2008, aravind chandu wrote: Hi, My question is Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems.One can access only from a system where it is already installed but not on the system where there is no sqlserver.Is postgresql similar to sql server or does it supports network sharing i,e one one can access postgresql from any system irrespective on which system it is installed. If there is any weblink for this kindly provide that also. Thank You, Avinash -- Louis Gonzales [EMAIL PROTECTED] http://www.linuxlouis.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
> Microsoft > sql server 2005 > cannot be shared on multiple systems i,e in a > network environment when > it is installed in one system it cannot be accessed > one other > systems. > Nonsense! Where did you get this stuff? I have even played with MS SQL Server 2005 Express, and it is not crippled in the way you describe. I am not a big fan of MS, but I have worked in shops where we used MS SQL Server 2005, and once the DB was set up, we could access it from anywhere. Since I often develop for it, I even have this capability, with MS SQL Server 2005, set up on the little LAN in my home office. > One can access only from a system where it > is already installed > but not on the system where there is no sqlserver.Is > postgresql similar > to sql server or does it supports network sharing > i,e one one can > access postgresql from any system irrespective on > which system it is > installed. > You can do this with any RDBMS I have seen. A RDBMS is of little commercial utility if you can't access it from other machines in a network. Mind you, I have worked with systems where the RDBMS was configured to respond only to apps on localhost, or a specific IP on the LAN, with access to the DB mediated through middleware. You should probably look at a) how your server is configured and b) how your client is configured (including whether or not you actually have client software on your client machine). Cheers Ted -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
At 4:15p -0400 on Tue, 03 Jun 2008, Aravind Chandu wrote: > Is postgresql similar to sql server or does it supports > network sharing i,e one one can access postgresql from any system > irrespective on which system it is installed. Postgres is an open source project and similarly is not bound by the same rules of business that Microsoft products are. Postgres has *no limitation* on number of connections, short of what your system can handle (network, memory, queries, disk, etc.). > If there is any weblink for this kindly provide that also. > Thank You, http://www.postgresql.org/docs/current/static/runtime-config-connection.html Should get you started. Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
On Tue, Jun 03, 2008 at 01:15:13PM -0700, aravind chandu wrote: > Microsoft sql server 2005 > cannot be shared on multiple systems i,e in a network environment when > it is installed in one system it cannot be accessed one other > systems.One can access only from a system where it is already installed > but not on the system where there is no sqlserver.Is postgresql similar > to sql server or does it supports network sharing i,e one one can > access postgresql from any system irrespective on which system it is > installed. If you mean, "If I have a host A and a host B, and A has Postgres running, can I connect from B and perform SQL on the data hosted on A," then the answer is, "Yes, provided you have the necessary programs to connect with." If you mean, "If I have host A and host B, can both A and B be simultaneous servers for the same database using shared storage?" the answer is, "No." A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] does postgresql works on distributed systems?
Hi, My question is Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems.One can access only from a system where it is already installed but not on the system where there is no sqlserver.Is postgresql similar to sql server or does it supports network sharing i,e one one can access postgresql from any system irrespective on which system it is installed. If there is any weblink for this kindly provide that also. Thank You, Avinash
Re: [GENERAL] E_PARSE error ?
Hi,
I think, this is the wrong list, it appears to be a PHP error.
Anyway, try to put the global $_SERVER['SCRIPT_NAME'] into {}brackets:
list($page_id)=sqlget("select page_id from pages where
name='{$_SERVER['SCRIPT_NAME']}'");
Hope, You're not lost anymore ...
Ludwig
PJ schrieb:
I'm using php5, postgresql 8.3, apache2.2.8, FreeBSD 7.0
I don't understand the message:
*Parse error*: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE,
expecting T_STRING or T_VARIABLE or T_NUM_STRING
the guilty line is:
list($page_id)=sqlget("
select page_id from pages where name='$_SERVER['SCRIPT_NAME']'");
the variable value is "/index.php"
however, at the time of execution this has been cleared
So, the question is - What is the unexpected T_ENCAPSED_AND_WHITESPACE?
and What is actually expected? Are we talking about the content of
$_SERVER['SCRIPT_NAME'] or what is the syntax error? This is within
php code; could it be that the parser is reading this as something
else, like HTML?
I'm lost :((
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcing Postgres to Execute a Specific Plan
All paths of optimizer are just in function "standard_planner", which mainly calls "subquery_planner", which just takes the rewrited structure "Query" as the main parameter. But system provides another way if you wannt to write your own optimizer, that is: define the global var "planner_hook" to your own optimizer function (please refer to function "planner"). So this is one of the way prevents the system takes it own optimizer routine. If you want to modify the plan returned by the optimizer, you can add some code just in the function "planner", i.e., takes result as the param of your routine. Any way, It is needed that you get very familiar with the structure of "PlannedStmt". ** 2008/6/3 John Cieslewicz <[EMAIL PROTECTED]>: I completely understand that what I am proposing is somewhat mad and I didn't expect it to be easy. Basically, I'm doing some research on a new operator and would like to start testing it by inserting it into a very specific place in very specific plans without having to do too much work in plan generation or optimization. I think that I could do this by writing some code to inspect a plan and swap out the piece that I care about. I realize this is a hack, but at the moment it's just for research purposes. Though I have worked with the internals of other db systems, I'm still getting familiar with postgres. Could such a piece of code be placed in the optimizer just before it returns an optimized plan or can a plan be modified after it is returned by the optimizer? John Cieslewicz.
[GENERAL] Re: PostgreSQL 8.3 XML parser seems not to recognize the DOCTYPE element in XML files
Bruce Momjian wrote: Added to TODO: * Allow XML to accept more liberal DOCTYPE specifications Is any form of DOCTYPE accepted? We're getting errors on the second line like this: http://host.domain/dtd/dotdisposition0_02.dtd";> The actual host.domain value is resolved by DNS, and wget of the url works on the machine. Attempts to cast the document to type xml give: ERROR: invalid XML content DETAIL: Entity: line 2: parser error : StartTag: invalid element name http://host.domain/dtd/dot ^ It would be nice to use the xml type, but we always have DOCTYPE -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PostgreSQL 8.3 XML parser seems not to recognize the DOCTYPE element in XML files
Bruce Momjian wrote: Added to TODO: * Allow XML to accept more liberal DOCTYPE specifications Is any form of DOCTYPE accepted? We're getting errors on a second line in an XML document that starts like this: http://host.domain/dtd/dotdisposition0_02.dtd";> The actual host.domain value is resolved by DNS, and wget of the url works on the server running PostgreSQL. Attempts to cast the document to type xml give: ERROR: invalid XML content DETAIL: Entity: line 2: parser error : StartTag: invalid element name http://host.domain/dtd/dot ^ It would be nice to use the xml type, but we always have DOCTYPE. I understand that PostgreSQL won't validate against the specified DOCTYPE, but it shouldn't error out on it, either. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PostgreSQL 8.3 XML parser seems not to recognize the DOCTYPE element in XML files
Bruce Momjian wrote: Added to TODO: * Allow XML to accept more liberal DOCTYPE specifications Is any form of DOCTYPE accepted? We're getting errors on the second line like this: http://host.domain/dtd/dotdisposition0_02.dtd";> The actual host.domain value is resolved by DNS, and wget of the url works on the machine. Attempts to cast the document to type xml give: ERROR: invalid XML content DETAIL: Entity: line 2: parser error : StartTag: invalid element name http://host.domain/dtd/dot ^ It would be nice to use the xml type, but we always have DOCTYPE -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bloom filter indexes?
On Tue, 2008-06-03 at 13:06 -0500, Mason Hale wrote: > On Tue, Jun 3, 2008 at 12:04 PM, Jeff Davis <[EMAIL PROTECTED]> wrote: > > On Tue, 2008-06-03 at 09:43 -0500, Mason Hale wrote: > >> I've been working on partitioning a rather large dataset into multiple > >> tables. One limitation I've run into the lack of cross-partition-table > >> unique indexes. In my case I need to guarantee the uniqueness of a > >> two-column pair across all partitions -- and this value is not used to > >> partition the tables. The table is partitioned based on a insert date > >> timestamp. > > > > You're looking for a constraint across tables. > > > > Yes, for this particular case. But I'm also interested in speeding up > cross-partition queries whether it is for a uniqueness check or not. > This uniqueness check is just one (important) instance of a > cross-partition query. I simple way to do this (potentially) would be to push the trigger to C. Joshua D. Drake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bloom filter indexes?
On Tue, Jun 3, 2008 at 12:04 PM, Jeff Davis <[EMAIL PROTECTED]> wrote: > On Tue, 2008-06-03 at 09:43 -0500, Mason Hale wrote: >> I've been working on partitioning a rather large dataset into multiple >> tables. One limitation I've run into the lack of cross-partition-table >> unique indexes. In my case I need to guarantee the uniqueness of a >> two-column pair across all partitions -- and this value is not used to >> partition the tables. The table is partitioned based on a insert date >> timestamp. > > You're looking for a constraint across tables. > Yes, for this particular case. But I'm also interested in speeding up cross-partition queries whether it is for a uniqueness check or not. This uniqueness check is just one (important) instance of a cross-partition query. >> To check the uniqueness of this value I've added an insert/update >> trigger to search for matches in the other partitions. This trigger is >> adding significant overhead to inserts and updates. > > Do you lock all of the tables before doing the check? If not, then you > have a race condition. > Yes, I was concerned about that. > It's possible this index strategy will be better for your case. > However, I think what you really want is some kind of multi-table > primary key. Have you considered storing the key in its own two-column > table with a UNIQUE index and having the partitions reference it? Thanks for the suggestion -- I'll explore maintaining the compound key in its own non-partitioned table. I was trying to avoid any application-layer code changes. I guess I can still accomplish that by updating this table via an insert/update trigger. But to reiterate, having bloom filter-based index would allow constant time determination of whether a given partition *may* contain the data. This would be very useful for large partitioned data-sets, especially in (very common) cases where performance is critical. This feature would also be useful for applications where data is partitioned (aka 'federated') across multiple servers. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bloom filter indexes?
On Tue, 2008-06-03 at 09:43 -0500, Mason Hale wrote: > I've been working on partitioning a rather large dataset into multiple > tables. One limitation I've run into the lack of cross-partition-table > unique indexes. In my case I need to guarantee the uniqueness of a > two-column pair across all partitions -- and this value is not used to > partition the tables. The table is partitioned based on a insert date > timestamp. You're looking for a constraint across tables. > To check the uniqueness of this value I've added an insert/update > trigger to search for matches in the other partitions. This trigger is > adding significant overhead to inserts and updates. Do you lock all of the tables before doing the check? If not, then you have a race condition. > This sort of 'membership test' where I need only need to know if the > key exists in the table is a perfect match for bloom filter. (see: > http://en.wikipedia.org/wiki/Bloom_filter). This is more of an implementation detail. Is a bloom filter faster than BTree in your case? > The Bloom filter can give false positives so using it alone won't > provide the uniqueness check I need, but it should greatly speed up > this process. False positives are OK, that's what RECHECK is for. It's possible this index strategy will be better for your case. However, I think what you really want is some kind of multi-table primary key. Have you considered storing the key in its own two-column table with a UNIQUE index and having the partitions reference it? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump: invalid memory alloc request size 4294967293
A few weeks back one of my PostgreSQL servers logged the following errors during the nightly dump: pg_dump: ERROR: invalid memory alloc request size 4294967293 pg_dump: SQL command to dump the contents of table "" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public. (gx, rx, cx, rxx, px, lx, vx, rxxx, sx, ex, cxx, ux, dx, rarrx) TO stdout; pg_dumpall: pg_dump failed on database "yyy", exiting The error repeated each night for a few nights and then stopped. Unfortunately I was not in a position to pursue the cause and effects of this error at the time they occurred. I have not found evidence of any problems with the regular operation of the server nor any evidence of data damage and this server continues to handle hundreds of thousands of transactions per day without fail. This server is in-queue for updates but is currently running PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20050901 (prerelease) (SUSE Linux). The table in question typically has 10,000-100,000+ inserts/day, a similar number of deletes due to a nightly move/archive process, and a handful of updates/day. I'd appreciate any advice on how I should pursue this (even if the advice is that it is OK to ignore at this point). Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] E_PARSE error ?
I'm using php5, postgresql 8.3, apache2.2.8, FreeBSD 7.0
I don't understand the message:
*Parse error*: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE,
expecting T_STRING or T_VARIABLE or T_NUM_STRING
the guilty line is:
list($page_id)=sqlget("
select page_id from pages where name='$_SERVER['SCRIPT_NAME']'");
the variable value is "/index.php"
however, at the time of execution this has been cleared
So, the question is - What is the unexpected T_ENCAPSED_AND_WHITESPACE?
and What is actually expected? Are we talking about the content of
$_SERVER['SCRIPT_NAME'] or what is the syntax error? This is within php
code; could it be that the parser is reading this as something else,
like HTML?
I'm lost :((
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Insert into master table ->" 0 rows affected" -> Hibernate problems
I have implemented partitioning using inheritance following the proposed solution here (using trigger): http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html My problem is that when my Hibernate application inserts to the master table, postgres returns "0 rows affected", which causes Hibernate to throw an exception since it expects the returned row count to be equal to the number of rows inserted. Is there a solution to this, i.e. to get Postgres to return the correct number of rows inserted to the master table?
[GENERAL] bloom filter indexes?
I've been working on partitioning a rather large dataset into multiple tables. One limitation I've run into the lack of cross-partition-table unique indexes. In my case I need to guarantee the uniqueness of a two-column pair across all partitions -- and this value is not used to partition the tables. The table is partitioned based on a insert date timestamp. To check the uniqueness of this value I've added an insert/update trigger to search for matches in the other partitions. This trigger is adding significant overhead to inserts and updates. This sort of 'membership test' where I need only need to know if the key exists in the table is a perfect match for bloom filter. (see: http://en.wikipedia.org/wiki/Bloom_filter). The Bloom filter can give false positives so using it alone won't provide the uniqueness check I need, but it should greatly speed up this process. Searching around for "postgresql bloom filter" I found this message from 2005 along the same lines: http://archives.postgresql.org/pgsql-hackers/2005-05/msg01475.php This thread indicates bloom filters are used in the intarray contrib module and the tsearch2 (and I assume the built-in 8.3 full-text search features). I also found this assignment for CS course at the University of Toronto, when entails using bloom filters to speed up large joins: http://queens.db.toronto.edu/~koudas/courses/cscd43/hw2.pdf So, my question: are there any general-purpose bloom filter implementations for postgresql? I'm particularly interested implementations that would be useful for partitioned tables. Is anyone working on something like this? thanks, - Mason -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join ... using ... and - is this expected behaviour?
On Tue, 3 Jun 2008, Rob Johnston wrote: > Just wondering if this is expected behaviour. When executing a query in > the form of: > > select column from table join table using (column) and column = clause > > pgsql (8.2) returns the following: syntax error at or near "and" > > Obviously, you can get around this by using "where" instead of "and", > but shouldn't the format as used above be valid? No. > The following is... > > select column from table join table on (column = column) and column = clause Yes. USING takes a column list, ON takes an expression as a search condition (and note from the syntax section that the parens are not always required around the expression). "(t1.col1 = t2.col1) AND col3 = foo" is still a valid search condition, but "(col1) AND col3 = foo" isn't a valid column list. > The documentation indicates that the two formats of the query are > equivalent > (http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN) It's talking about the execution, not the syntax, but that could probably be more clear. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database growing. Need autovacuum help.
On Tue, Jun 3, 2008 at 7:41 AM, Henrik <[EMAIL PROTECTED]> wrote: > > To be able to handle versions we always insert new folders even though > nothing has changed but it seemd like the best way to do it. > > E.g > > First run: >tbl_file 500k new files. >tbl_folder 50k new rows. >tbl_file_folder 550k new rows. > > Second run with no new files. >tbl_file unchanged. >tbl_folder 50k new rows >tbl_file_folder 550k new rows. On useful trick is to include a where clause that prevents the extra updates. I.e. update table set field=123 where field <> 123; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Failing to recover after panic shutdown
Per Lauvås wrote: > Hi > > I am running Postgres 8.2 on Windows 2003 server SP2. > > Every now and then (2-3 times a year) our Postgres service is down > and we need to manually start it. This is what we find: > > In log when going down: > 2008-06-02 13:40:02 PANIC: could not open file > "pg_xlog/0001001C0081" (log file 28, segment 129): > Invalid argument Are you by any chance running an antivirus or other "security software" on this server? > We are archiving WAL-segments at a remote machine, and we are copying > non-filled WAL-segments every 10 minutes to be able to rebuild the DB > with a maximum of 10 minutes of missing data. (I don't know if that > has anything to do with it). How are you copying these files? Are you saying you're actually copying the files out of the pg_xlog directory, or are you using the archive_command along with archive_timeout? //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange statistics
Hi list,
I'm having a table with a lots of file names in it. (Aprox 3 million)
in a 8.3.1 db.
Doing this simple query shows that the statistics is way of but I can
get them right even when I raise the statistics to 1000.
db=# alter table tbl_file alter file_name set statistics 1000;
ALTER TABLE
db=# analyze tbl_file;
ANALYZE
db=# explain analyze select * from tbl_file where lower(file_name)
like lower('to%');
QUERY PLAN
Bitmap Heap Scan on tbl_file (cost=23.18..2325.13 rows=625
width=134) (actual time=7.938..82.386 rows=17553 loops=1)
Filter: (lower((file_name)::text) ~~ 'to%'::text)
-> Bitmap Index Scan on tbl_file_idx (cost=0.00..23.02 rows=625
width=0) (actual time=6.408..6.408 rows=17553 loops=1)
Index Cond: ((lower((file_name)::text) ~>=~ 'to'::text) AND
(lower((file_name)::text) ~<~ 'tp'::text))
Total runtime: 86.230 ms
(5 rows)
How can it be off by a magnitude of 28??
Cheers,
Henke
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database growing. Need autovacuum help.
3 jun 2008 kl. 15.23 skrev Bill Moran: In response to Henrik <[EMAIL PROTECTED]>: We are running a couple of 8.3.1 servers and the are growing a lot. I have the standard autovacuum settings from the 8.3.1 installation and we are inserting about 2-3 million rows every night and cleaning out just as many every day. Is this a batch job? If so, autovac might not be your best friend here. There _are_ still some cases where autovac isn't the best choice. If you're doing a big batch job that deletes or updates a bunch of rows, you'll probably be better off making a manual vacuum the last step of that batch job. Remember that you can vacuum individual tables. Well, sort of. We have different jobs that usually runs at night filling the database with document information. After that is dont we have maintenance jobs that clean out old versions of those documents. Maybe autovacuum is not for us on at least this table. I know that it is an specific table that has most bloat. The database size rose to 80GB but after a dump/restore its only 16GB which shows that there where nearly 65GB bloat in the database. Does it keep growing beyond 80G? While 65G may seem like a lot of bloat, it may be what your workload needs as working space. I mean, you _are_ talking about shifting around 2-3 million rows/day. Crank up the logging. I believe the autovac on 8.3 can be configured to log exactly what tables it operates on ... which should help you determine if it's not configured aggressively enough. I will do that. But I already which table is the bad boy in this case. :) If it's just a single table that's bloating, a VACUUM FULL or CLUSTER of that table alone on a regular schedule might take care of things. If your data is of a FIFO nature, you could benefit from the old trick of having two tables and switching between them on a schedule in order to truncate the one with stale data in it. It is somewhat FIFO but I can't guarantee it... I will look at CLUSTER and see. Maybe de design is flawed :) To put it simple we have a document storing system and the 3 major table is tbl_folder, tbl_file and the many-to-many table tbl_file_folder. In the tbl_file we only have unique documents. But a file can be stored in many folders and a folder can have many files so we have the tbl_file_folder with fk_file_id and fk_folder_id. To be able to handle versions we always insert new folders even though nothing has changed but it seemd like the best way to do it. E.g First run: tbl_file 500k new files. tbl_folder 50k new rows. tbl_file_folder 550k new rows. Second run with no new files. tbl_file unchanged. tbl_folder 50k new rows tbl_file_folder 550k new rows. The beauty with this is that it is very effective to retrieve the exact file/folder structure at a given point in time but the drawback is that it is a lot of overhead in the database. Maybe someone has some kool new idea about this. :) Thanks Bill! Cheers, henke Hope some of these ideas help. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database growing. Need autovacuum help.
In response to Henrik <[EMAIL PROTECTED]>: > > We are running a couple of 8.3.1 servers and the are growing a lot. > > I have the standard autovacuum settings from the 8.3.1 installation > and we are inserting about 2-3 million rows every night and cleaning > out just as many every day. Is this a batch job? If so, autovac might not be your best friend here. There _are_ still some cases where autovac isn't the best choice. If you're doing a big batch job that deletes or updates a bunch of rows, you'll probably be better off making a manual vacuum the last step of that batch job. Remember that you can vacuum individual tables. > The database size rose to 80GB but after a dump/restore its only 16GB > which shows that there where nearly 65GB bloat in the database. Does it keep growing beyond 80G? While 65G may seem like a lot of bloat, it may be what your workload needs as working space. I mean, you _are_ talking about shifting around 2-3 million rows/day. Crank up the logging. I believe the autovac on 8.3 can be configured to log exactly what tables it operates on ... which should help you determine if it's not configured aggressively enough. Some other things to do to improve your situation are to isolate exactly _what_ is bloating. Use pg_relation_size() to get a list of the sizes of all DB objects right after a dump/restore and when the database is bloated and compare to see what's bloating the most. Don't forget to check indexes as well. If rebuilding a single index nightly will take care of your bloat, that's not bad. Unusual, but it does happen under some workloads ... you might be able to adjust the index fill factor to improve things as well. If it's just a single table that's bloating, a VACUUM FULL or CLUSTER of that table alone on a regular schedule might take care of things. If your data is of a FIFO nature, you could benefit from the old trick of having two tables and switching between them on a schedule in order to truncate the one with stale data in it. Hope some of these ideas help. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Failing to recover after panic shutdown
Hi I am running Postgres 8.2 on Windows 2003 server SP2. Every now and then (2-3 times a year) our Postgres service is down and we need to manually start it. This is what we find: In log when going down: 2008-06-02 13:40:02 PANIC: could not open file "pg_xlog/0001001C0081" (log file 28, segment 129): Invalid argument This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2008-06-02 13:40:02 LOG: server process (PID 5792) exited with exit code 3 2008-06-02 13:40:02 LOG: terminating any other active server processes 2008-06-02 13:40:02 WARNING: terminating connection because of crash of another server process 2008-06-02 13:40:02 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2008-06-02 13:40:02 HINT: In a moment you should be able to reconnect to the database and repeat your command. (WARNING, DETAIL and HINT is repeated a few times...) 2008-06-02 13:40:02 LOG: all server processes terminated; reinitializing 2008-06-02 13:40:02 LOG: database system was interrupted at 2008-06-02 13:39:39 W. Europe Daylight Time 2008-06-02 13:40:02 LOG: checkpoint record is at 1C/80F646B0 2008-06-02 13:40:02 LOG: redo record is at 1C/80F646B0; undo record is at 0/0; shutdown FALSE 2008-06-02 13:40:02 LOG: next transaction ID: 0/316291661; next OID: 7343 2008-06-02 13:40:02 LOG: next MultiXactId: 1929; next MultiXactOffset: 4093 2008-06-02 13:40:02 LOG: database system was not properly shut down; automatic recovery in progress 2008-06-02 13:40:02 LOG: redo starts at 1C/80F64700 2008-06-02 13:40:02 LOG: unexpected pageaddr 1C/7900 in log file 28, segment 129, offset 0 2008-06-02 13:40:02 LOG: redo done at 1C/80FFD6E0 2008-06-02 13:40:02 FATAL: the database system is starting up 2008-06-02 13:40:03 FATAL: the database system is starting up 2008-06-02 13:40:03 FATAL: the database system is starting up 2008-06-02 13:40:03 FATAL: the database system is starting up 2008-06-02 13:40:03 FATAL: could not open file "pg_xlog/0001001C0081" (log file 28, segment 129): Invalid argument 2008-06-02 13:40:03 FATAL: the database system is starting up 2008-06-02 13:40:03 LOG: startup process (PID 4420) exited with exit code 1 2008-06-02 13:40:03 LOG: aborting startup due to startup process failure 2008-06-02 13:40:05 LOG: archived transaction log file "0001001C0080" 2008-06-02 13:40:05 LOG: logger shutting down In log when manually started: 2008-06-02 13:50:34 LOG: database system was shut down at 2008-06-02 13:40:03 W. Europe Daylight Time 2008-06-02 13:50:34 LOG: checkpoint record is at 1C/80FFE990 2008-06-02 13:50:34 LOG: redo record is at 1C/80FFE990; undo record is at 0/0; shutdown TRUE 2008-06-02 13:50:34 LOG: next transaction ID: 0/316292191; next OID: 7343 2008-06-02 13:50:34 LOG: next MultiXactId: 1929; next MultiXactOffset: 4093 2008-06-02 13:50:34 LOG: database system is ready 2008-06-02 13:50:35 LOG: archived transaction log file "0001001C0080" 2008-06-02 13:50:54 LOG: archived transaction log file "0001001C0080" We are archiving WAL-segments at a remote machine, and we are copying non-filled WAL-segments every 10 minutes to be able to rebuild the DB with a maximum of 10 minutes of missing data. (I don't know if that has anything to do with it). Does anyone have a clue on what the problem could be? Vennlig hilsen Per Lauvås Systemutvikler Fax: +47 22 41 60 61 Direct: +47 24 15 55 51 Mintra as Storgata 1 P.O. Box 8945 Youngstorget N-0028 Oslo Tel: +47 24 15 55 00 Fax: +47 22 41 60 61 http://www.mintra.no Faglig forum: Helhetlig læring og kompetanse Motta Mintras faglige e-postmagasin Helhetlig LÆRING & KOMPETANSE - gratis hver måned. Svar på denne e-posten med "HLK" i emnefeltet eller se www.mintra.no for å melde din interesse. Hyllevare e-læringskurs på Trainingportal.no Få tilgang til Mintras hyllevarer av e-læringskurs innen HMS, applikasjonsopplæring, prosjektledelse, teknisk opplæring med mer på www.trainingportal.no
[GENERAL] join ... using ... and - is this expected behaviour?
Just wondering if this is expected behaviour. When executing a query in the form of: select column from table join table using (column) and column = clause pgsql (8.2) returns the following: syntax error at or near "and" Obviously, you can get around this by using "where" instead of "and", but shouldn't the format as used above be valid? The following is... select column from table join table on (column = column) and column = clause The documentation indicates that the two formats of the query are equivalent (http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN) The following test case illustrates the issue: CREATE TABLE table1 ( columnone integer, columntwo integer ); CREATE TABLE table2 ( columntwo integer, columnthree integer ); insert into table1 values (1, 1), (2, 1); insert into table2 values (1, 3); This query results in a syntax error: select t1.columnone, t1.columntwo, t2.columnthree from table1 t1 join table2 t2 using (columntwo) and columnone = 1 This query executes as expected: select t1.columnone, t1.columntwo, t2.columnthree from table1 t1 join table2 t2 on (t1.columntwo = t2.columntwo) and columnone = 1 Rob Johnston -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FW: make rows unique across db's without UUIP on windows?
In response to "Kimball Johnson" <[EMAIL PROTECTED]>: > > What is the normal solution in pgsql-land for making a serious number of > rows unique across multiple databases? > > > > I mean particularly databases of different types (every type) used at > various places (everywhere) on all platforms (even MS[TM])? You know. a > UNIVERSAL id? Just give each separate system it's own unique identifier and a sequence to append to it. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database growing. Need autovacuum help.
Hi List, We are running a couple of 8.3.1 servers and the are growing a lot. I have the standard autovacuum settings from the 8.3.1 installation and we are inserting about 2-3 million rows every night and cleaning out just as many every day. The database size rose to 80GB but after a dump/restore its only 16GB which shows that there where nearly 65GB bloat in the database. #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 20 #autovacuum_vacuum_cost_limit = -1 And also max_fsm_pages = 30 #max_fsm_relations = 1000 Any pointers would be greatly appreciated. Cheers, Henke -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot drop user (PostgreSQL 8.1.11)
I recall I came across similar issue on older (8.1 or 8.2) versions of
PostgreSQL some time ago. DB was pretty small so I dump-restored it
eventually, but it looks like a bug anyway.
I cannot reproduce it at 8.3.
--
Regards,
Ivan
On Mon, Jun 2, 2008 at 7:12 PM, Maxim Boguk <[EMAIL PROTECTED]> wrote:
> I trying drop old user but got some strange issues:
>
> template1=# drop USER szhuchkov;
> ERROR: role "szhuchkov" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database billing
> 2 objects in database shop
>
> ok... lets look closer these two DB:
>
>
> shop=# drop USER szhuchkov;
> ERROR: role "szhuchkov" cannot be dropped because some objects depend on it
> DETAIL: owner of type pg_toast.pg_toast_406750
> owner of type pg_toast.pg_toast_1770195
> 1 objects in database billing
>
> hm damn strange...
>
> shop=# SELECT * from pg_user where usename='szhuchkov';
> usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
> valuntil | useconfig
> ---+--+-+--+---+--+--+
> szhuchkov |16387 | f | f| f | |
> | {"search_path=bill, billstat"}
> (1 запись)
>
> Lets look pg_type for these two types:
>
> shop=# SELECT typname,typowner from pg_type where typname IN
> ('pg_toast_406750', 'pg_toast_1770195');
> typname | typowner
> --+--
> pg_toast_1770195 | 10
> pg_toast_406750 | 10
> (записей: 2)
>
> owner right (pgsql)
>
>
>
> Lets look db billing:
>
> billing=# drop USER szhuchkov;
> ERROR: role "szhuchkov" cannot be dropped because some objects depend on it
> DETAIL: owner of function vz_vds_ip_add(integer,bigint)
> 2 objects in database shop
>
> billing=# SELECT proname,proowner from pg_proc where proname like
> '%vz_vds_ip_add%';
>proname| proowner
> ---+--
> vz_vds_ip_add | 10
> (1 запись)
>
> ok... again right owner... no signs of szhuchkov
>
> Last test... lets try pg_dumpall -s :
>
>
> [EMAIL PROTECTED] /home/mboguk]$ pg_dumpall -s | grep szhuchkov
> CREATE ROLE szhuchkov;
> ALTER ROLE szhuchkov WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
> PASSWORD '***';
> ALTER ROLE szhuchkov SET search_path TO bill, billstat;
> GRANT bill1c_r TO szhuchkov GRANTED BY pgsql;
> GRANT bill_r TO szhuchkov GRANTED BY pgsql;
> GRANT billexch_r TO szhuchkov GRANTED BY pgsql;
> GRANT billstat_r TO szhuchkov GRANTED BY pgsql;
> GRANT shop_r TO szhuchkov GRANTED BY pgsql;
> GRANT templar_r TO szhuchkov GRANTED BY pgsql;
>
> Nothing more... so according pg_dumpall szhuchkov also doesnt have any
> active objects in DB.
>
> In all other sides DB work 24x7 well without any other issues
> (and because 24x7 requirements i cannot stop DB and drop user from single
> user mode).
>
> Any ideas? or what to check else?
>
> --
> Maxim Boguk
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] turning fsync off for WAL
> > > Ahh. I think you can use this effectively but not the way you're > describing. > > Instead of writing the wal directly to persistentFS what I think you're > better > off doing is treating persistentFS as your backup storage. Use "Archiving" > as > described here to archive the WAL files to persistentFS: > > > http://postgresql.com.cn/docs/8.3/static/runtime-config-wal.html#GUC-ARCHIVE-MODE > Looks like this is the best solution. Thanks, Ram
Re: [GENERAL] turning fsync off for WAL
"Ram Ravichandran" <[EMAIL PROTECTED]> writes: > The problem that I am facing is that EC2 has no persistent storage (at least > currently). So, if the server restarts for some reason, all data on the > local disks are gone. The idea was to store the tables on the non-persistent > local disk, and do the WAL on to an S3 mounted drive. If the server goes > down for some reason, I was hoping to recover by replaying the WAL. I was > hoping that by faking the fsyncs, I would not incur the actual charges from > Amazon until the file system writes into S3. > Also, since WAL is on a separate FS, it will not affect my disk-write > rates. Ahh. I think you can use this effectively but not the way you're describing. Instead of writing the wal directly to persistentFS what I think you're better off doing is treating persistentFS as your backup storage. Use "Archiving" as described here to archive the WAL files to persistentFS: http://postgresql.com.cn/docs/8.3/static/runtime-config-wal.html#GUC-ARCHIVE-MODE Then if your database goes down you'll have to restore from backup (stored in persistentFS) and then run recovery from the archived WAL files (from persistentFS) and be back up. You will lose any transactions which haven't been archived yet but you can control how many transactions you're at risk of losing versus how much you pay for all the "puts". The more "puts" the fewer transactions you'll be putting at risk but the more you'll pay. You can also trade off paying for more frequent "puts" of hot backup images (make sure to read how to use pg_start_backup() properly) against longer recovery times. TANSTAAFL :( If you do this then you may as well turn fsync off on the server since you're resigned to having to restore from backup on a server crash anyways... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Offre d'emploi DBA/architecte
Bonjour à tous, Ci-dessous une offre d'emploi autour de PostgreSQL. EDD, société leader dans la diffusion des contenus de presse dématérialisés et partenaire privilégié des entreprises de presse françaises, recherche un architecte/administrateur de bases de données PostgreSQL. Au sein de la division Exploitation, vos domaines d'intervention seront de trois type : Optimisation Vous ferez évoluer les bases de données existantes (MySQL, Oracle, PostgreSQL, ...) et optimiserez leur structure en adéquation avec l'évolution des besoins. Vous optimiserez les process d'alimentation et de mise à jour des données. Maintenance Dans le cadre de la gestion quotidienne des systèmes, vous aurez en charge leur surveillance et veillerez au suivi des procédures d'administration. Conception En collaboration avec le service des Développements Informatiques, vous serez force de proposition dans la conception des futures applications et contribuerez à la rédaction des cahiers des charges. Vous faites preuve de bonnes capacités d'analyse et de synthèse, et vous avez une bonne connaissance des langages de développement sollicitant les bases de données (Perl, Python, PHP,...). La rigueur, la méthode, sont des qualités nécessaires pour mener à bien ces missions, dans un environnement de données sensibles. Adressez votre CV, lettre de motivation et tous les éléments que vous jugerez pertinents pour accompagner votre candidature à [EMAIL PROTECTED] -- Fabien Grumelard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] turning fsync off for WAL
On Tue, 2008-06-03 at 00:04 -0400, Ram Ravichandran wrote: > This seems like a much better idea. So, I should > a) disable synchronous_commit > b) set wal_writer_delay to say 1 minute (and leave fsync on) > c) symlink pg_xlog to the PersistentFS on S3. > a) sounds good. b) has a max setting of 10 seconds, which I think is a realistic maximum in this case also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
