Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 12:34 -0700, Darren Duncan wrote: Yes, but that would just be in-memory or in temporary places external to every database. On disk internal to a database there would just be the oid. In fact, another aspect of the database model I defined is that each database is entirely self-contained; while you can do cross-database queries, you don't have cross-database constraints, in the general case. Yes, you can have a local oid and a fully-qualified oid. It sounds like it might take some effort (which is an understatement) to go through the system and figure out which ones should be local and which ones should be fully-qualified. 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
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: If for some reason we needed to have tables that happened to be called x.y.z and a.b.c accessible from a single SQL session, we could allow that much more simply by allowing schemas to be nested. Then we could allow arbitrary numbers of levels, not just three. FWIW, I actually tried to do that back when we first introduced schema support (the fact that the code calls them namespaces and not schemas is a leftover from that idea). It turns out to be a whole lot harder than it sounds, because of the ambiguity you get about which name goes at what level. A simple example of this is: if you write x.y in a query, is that meant to be table x's column y, or is it meant to be field y within a composite column x of some table in the query? We've resolved that by requiring you to write (x).y when you mean the latter, but it's not exactly an intuitive or pleasant answer. In the same way, if namespaces can be nested to different levels, it gets really messy to support abbreviations of any sort --- but the SQL spec requires us to be able to do so. What if you used the context of the calling code and resolve in favor of whatever match is closest to it? The problem is related to general-purpose programming languages. Basically start looking in the lexical context for an x and if you find one use that; otherwise, assuming we're talking about referencing code that lives in the database such as a function, look at the innermost schema containing the referencing code and see if it has a direct child named x; otherwise go up one level to a parent schema, and so on until you get to the top, and finding none by then say it doesn't exist. If there are several x in this search sequence, only use the first one regardless of whether it has a y, so to prevent bugs from too much complexity. Same for just looking for x by itself in fact, not just an x.y. For the case of calling code that doesn't live in the database such as a client-side query, I believe there are session variables like current schema or such, and you can use this as the starting point for the search for x, looking first at what that schema directly contains, and then its parent, and so on. Something like that. Or ignore what I said about starting in a lexical context and do what you already do there, but keep what I said about relative order of schemas to search, only searching direct children of ancestors of the current code's context schema starting with the current context. You could also come up with some relative name syntax such as filesystems support with their ../ and such, but that's further from standard SQL. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 21:04 -0700, Darren Duncan wrote: I think you should make more of an effort to understand how the system works now, and why, before proposing radical redesigns. Well yes, of course. But that will take time and I think I already understand enough about it to make some useful contributions in the meantime. How much or what I already know may not always come across well. If this bothers people then I can make more of an effort to reduce my input until I have more solid things to back them up. I don't think anyone expects you to understand all the internal APIs in postgres before you make a proposal. But we do expect you to look critically at your own proposals with the status quo (i.e. existing code, users, and standards) in mind. And that probably means poking at the code a little to see if you find stumbling blocks, and asking questions to try to trace out the shape of the project. I'm hoping that we can learn a lot from your work on Muldis D. In particular, the type system might be the most fertile ground -- you've clearly done some interesting things there, and I think we've felt some pressure to improve the type system from a number of different projects*. Regards, Jeff Davis * That being said, PostgreSQL's type system is actually very good. Consider the sophisticated type infrastructure (or at least plumbing around the type system) required to make KNN-GiST work, for instance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] New feature: cached foreign keys
Hi. Today i have an idea for increase performance of foreign keys. After search parent record, store ctid in shared memory. Subsequent searches look first to the record at stored ctid, and when it is deleted do regular search using index. Pro: faster searching for common keys when parent table is constant. Less locks on index. Contra: slower searching when parent table is heavy updated. More memory used for cached ctid's. -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On 9/07/2011 11:27 AM, Robert Haas wrote: On Fri, Jul 8, 2011 at 2:21 AM, Darren Duncandar...@darrenduncan.net wrote: I think an even better way to support this is would be based on Postgres having support for directly using multiple databases within the same SQL session at once, as if namespaces were another level deep, the first level being the databases, the second level the schemas, and the third level the schema objects. Kind of like what the SQL standard defines its catalog/schema/object namespaces. This instead of needing to use federating or that contrib module to use multiple Pg databases of the same cluster at once. But if that's what you want, just don't put your data in different databases in the first place. That's what schemas are for. I think the part missing from that is that Pg does not currently provide a mechanism to connect directly to a schema within a particular database. You can log in and set search_path, of course, but it's a wee bit clumsy and I suspect lots of people just don't get that. pg_hba.conf cannot control schema access, either, so access control based on IP address range or allowing different kinds of auth for different users cannot be controlled on a schema level. Being able to connect to a database.schema location and have Pg connect to the database then auto-set the search_path would address many if not all of the use cases for cross-database queries. That said, if there's ever a facility to WAL certain databases separately and/or have different replication for different databases within the same cluster, I can easily see the need coming up for big-unimportant-unreplicated-database needing to query stuff from small-vital-replicated-database. By then, though, SQL-MED should fill that need quite well enough. Being able to: psql dbname.schemaname or jdbc:postgresql://localhost/dbname.schemaname/ would probably address most of the other use cases, and make it much easier for people migrating from databases that support cross-DB queries. Thoughts? -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On 9/07/2011 2:39 PM, Darren Duncan wrote: What if you used the context of the calling code and resolve in favor of whatever match is closest to it? *BAD* idea IMHO. It sounds attractive at first, but inevitably leads to problems where a query used to work until someone creates a table/type/whatever that's closer and suddenly things explode. The existing search_path feature already suffers from issues like that, and it's usually better to explicitly fully qualify names when you're not just writing interactive code. The same potential foot-gun can be exploited as an excellent and useful feature when you actually *want* the same name to point to different things in different contexts, but in general use it's more likely to create situations where change A breaks unrelated query B. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] New feature: cached foreign keys
On 9/07/2011 3:06 PM, pasman pasmański wrote: Hi. Today i have an idea for increase performance of foreign keys. After search parent record, store ctid in shared memory. Subsequent searches look first to the record at stored ctid, and when it is deleted do regular search using index. How many do you keep cached at a time? When do you evict the cached ctid from memory? What about if someone else deletes that parent tuple? Do you notify all backends whenever any tuple that could potentially be a foreign key target is deleted so they can check their caches and flush it if it's present? This is a *REALLY *HARD* problem unless you can narrow it to a specific case with clear and simple rules about what you cache, concurrency, how long something stays cached, etc. The cache you describe superficially seems like an easy and nice way to do things but in practice this sort of thing usually works out to be really, really, really complicated. There are only two hard problems in Computer Science: cache invalidation and naming things. -- Phil Karlton To learn more about why it's so hard, see: http://en.wikipedia.org/wiki/Cache_invalidation -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] New feature: cached foreign keys
Reality is crude, seems than this idea is not as good as i think :( Thanks for answer. 2011/7/9, Craig Ringer cr...@postnewspapers.com.au: On 9/07/2011 3:06 PM, pasman pasmański wrote: Hi. Today i have an idea for increase performance of foreign keys. After search parent record, store ctid in shared memory. Subsequent searches look first to the record at stored ctid, and when it is deleted do regular search using index. How many do you keep cached at a time? When do you evict the cached ctid from memory? What about if someone else deletes that parent tuple? Do you notify all backends whenever any tuple that could potentially be a foreign key target is deleted so they can check their caches and flush it if it's present? This is a *REALLY *HARD* problem unless you can narrow it to a specific case with clear and simple rules about what you cache, concurrency, how long something stays cached, etc. The cache you describe superficially seems like an easy and nice way to do things but in practice this sort of thing usually works out to be really, really, really complicated. There are only two hard problems in Computer Science: cache invalidation and naming things. -- Phil Karlton To learn more about why it's so hard, see: http://en.wikipedia.org/wiki/Cache_invalidation -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- 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] New feature: cached foreign keys
On 9/07/2011 8:26 PM, pasman pasmański wrote: Reality is crude, seems than this idea is not as good as i think :( Thanks for answer. No worries. For what it's worth, PostgreSQL caches recently used tuples in shared memory anyway. The OS caches disk data in RAM too. So if the foreign key is often checked and frequently used, it will often stay in cache. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unintuitive behavior regarding inheritance
Hi all; I started trying to use table partitioning to handle a rather odd case in the software I am working on. I ran into an issue (one I can correct in my code) that strikes me as extremely unintuitive. I figured I would report it here as behavior I would like to see change. The basic problem is that while nearly all table contraints are inherited, unique constraints are not. This means that primary keys and so forth end up having to be redefined on all child tables explicitly as part of the table creation process. This is sufficiently unintuitive that as a result I am having to go back and amend contributions of people far more knowledgeable on this than I am. It would be really helpful in the future if unique constraints and primary keys were inherited. At least I can adjust code, but this is a pretty big gotcha which could go unnoticed until you have duplicates for primary key fields in specific child tables. Best wishes, Chris Travers -- 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] Unintuitive behavior regarding inheritance
On Sat, Jul 9, 2011 at 6:09 AM, Guillaume Lelarge guilla...@lelarge.info wrote: To have a primary key or a unique key on an partitioned table, it would mean that we should be able to have one index on multiple tables. Because primary key and unique constraints are enforced with an index. That's not something easy to do, and I guess it would make the index bigger, which isn't performance savvy. I don't think you necessarily have to go as far as creating cross-table indexes. In the case I am looking at, one of the values I partition on is part of the primary key, so collisions across partitions can be avoided in this way. However, the problem here is that this also makes it far more difficult to create partitioned tables which reference keys on table partitions because those keys have to be defined on each partition. Simply creating per-partition indexes would be sufficient for that use case. Otherwise it becomes relatively maintenance heavy and quite error prone. I agree that this wouldn't get us to what would make everyone happy, but it would create reasonable workarounds for when one needs fkeys against partitioned tables Best Wishes, Chris Travers -- 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] Unintuitive behavior regarding inheritance
On Jul 9, 2011, at 9:21, Chris Travers chris.trav...@gmail.com wrote: On Sat, Jul 9, 2011 at 6:09 AM, Guillaume Lelarge guilla...@lelarge.info wrote: To have a primary key or a unique key on an partitioned table, it would mean that we should be able to have one index on multiple tables. Because primary key and unique constraints are enforced with an index. That's not something easy to do, and I guess it would make the index bigger, which isn't performance savvy. I don't think you necessarily have to go as far as creating cross-table indexes. In the case I am looking at, one of the values I partition on is part of the primary key, so collisions across partitions can be avoided in this way. However, the problem here is that this also makes it far more difficult to create partitioned tables which reference keys on table partitions because those keys have to be defined on each partition. Simply creating per-partition indexes would be sufficient for that use case. Otherwise it becomes relatively maintenance heavy and quite error prone. I agree that this wouldn't get us to what would make everyone happy, but it would create reasonable workarounds for when one needs fkeys against partitioned tables Table inheritance has it's flaws. They are well-documented and often discussed. Your last two paragraphs and unintelligible to me. If you FK the parent table in an Inheritance scheme the system will only check the parent table and not any inheritors. While I am unfamiliar with why indexes and FK constraints are not copied they can be added quite easily manually to any table I create, and from the docs I am fully aware they are indeed not copied. And yes, I understand that because something is written doesn't mean it is understood. Understanding usually comes via experience which you either earn or recruit. Partitioning is an advanced feature with, in it's current implementation, enough caveats to make it dangerous to use. But, band-aids are not going to be enough. A full graft is needed in order to maintain backward compatibility while implementing a system that meets the identified needs of the community. David J. -- 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] Unintuitive behavior regarding inheritance
Ok So what I am trying to do is: 1) Paritioned tables 2) Joins against the table partitions The way I have this done is: The first table is partitioned on the basis of one part of the primary key. So in theory since nobody has permission to insert into the base table, all records should have unique primary keys in the inheritance tree. The join table against the partition table is more complex and I had to partition it across two pieces of the primary key. To be clear I am joining a partitioned table against a partitioned table (since that seems to be the only sane way of joining against a partitioned table if referential integrity has to be enforced. Basically imagine the following (the actual schema is quite a bit more complex): create table invoice ( id serial primary key, reference text, ); create table order (id serial primary key, reference text, ); create table file_class ( id serial not null unique, label text primary key ); create table file_attachment ( id serial not null unique, file_class int references file_class(id), ref_key int, file_name text, primary key(file_class, ref_key, file_name) ); create table invoice_attachment ( check (file_class = 1), foreign key(ref_key) references invoice(id), ) inherits (file_attachment); create table order_attachment ( check(file_class = 2), foreign key (ref_key) references order(id), ) inherits (file_attachment); create table order_to_invoice_file_map( src_class int references invoice_attachment(id), dest_class int references order_attachment(id), file_id int references order_attachment(id), ); -- this is actually inherited in my schema too It seems for this to work all primary and foreign key constraints have to be redeclared on each child table. OTOH check constraints are additive. The idea here is to provide a system where by a consistent relational interface is provided for file attachments, and where neither higher levels of the application nor pieces of the model have to be aware of the existance or not of partitions. Ideally given the fact that each child table constrains part of the primary key to a reserved set of values means I shouldn't have to worry about primary key collision across the inheritance tree. Unfortunately, it seems that to do this a lot of copy/paste is required. The major drawback with this approach is that since check constraints are always inherited they can't be used to enforce the idea that base tables should have no rows. At the same time, since unique constraints are never inherited they have to be redeclared on every child. I recognize that one of the big hurdles here is the need sometimes to drop indexes while loading data but at the same time there are several places where this sort of thing has been overlooked so far by people far more knowledgeable about PostgreSQL than I am. Hope this helps. Chris Travers -- 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] Unintuitive behavior regarding inheritance
create table invoice_attachment ( check (file_class = 1), foreign key(ref_key) references invoice(id), ) inherits (file_attachment); create table order_attachment ( check(file_class = 2), foreign key (ref_key) references order(id), ) inherits (file_attachment); While I get your proposal I am not a developer so I cannot really comment as to the cost-benefit of implementing it but my take is that there is too much variety to effectively code the automation you desire (ignoring the fact that you'd want the indexes to remain independent) and so it is left to the developer to specify exactly what is desired. The real issue is that your child tables are distinct sub-types of the parent as opposed to being identical to the parent in all ways except for the range of allowable values- which what a partition is and the driver behind the current inheritance implementation. It would have been better if they had restricted partitions such that you could not add columns and used a syntax such as Create Table () PARTITION OF (parent_table) and not even attempt to support object-like inheritance. As it is now object-inheritance is only partially supported and so while you can fake sub-classing the database is incapable of properly enforcing the normal use cases. A generally better way to implement object-inheritance is to use one-to-one tables and encapsulate using functions/view/triggers and possibly rules. Suggestions and ideas are encouraged but are more readily received if they at least acknowledge that there are reasons for the existing behavior and that any solution needs to addres more than the single problem that is driving the suggestion, and that it generally wants to solve the problem without introducing more problems/complexity. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 23:39 -0700, Darren Duncan wrote: What if you used the context of the calling code and resolve in favor of whatever match is closest to it? The problem is related to general-purpose programming languages. Basically start looking in the lexical context for an x and if you find one use that; otherwise, assuming we're talking about referencing code that lives in the database such as a function, look at the innermost schema containing the referencing code and see if it has a direct child named x; otherwise go up one level to a parent schema, and so on until you get to the top, and finding none by then say it doesn't exist. This is an example of where data languages and normal programming languages have a crucial difference. With a data language, you have this problem: 1. An application uses a query referencing 'y.z.foo' that resolves to internal object with fully-qualified name 'x.y.z'. 2. An administrator creates object 'y.z.foo'. Now, the application breaks all of a sudden. In a normal prgramming language, if the schema of the two foos are different, the compiler could probably catch the error. SQL really has no hope of catching it though. PostgreSQL has this problem now in a couple ways, but it's much easier to grasp what you might be conflicting with. If you have multiple nested levels to traverse and different queries using different levels of qualification, it gets a little more messy and I think a mistake is more likely. 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] No suitable driver found for jdbc:postgresql [error]
1. Upon logging to *SQL shell* the following is being displayed:
*Server [localhost]:*
*Database [postgres]:*
*Port [5432]:*
*Username [postgres]:*
*psql (9.0.4)*
*WARNING: Console code page (437) differs from Windows code page (1252)*
* 8-bit characters might not work correctly. See psql reference*
* page Notes for Windows users for details.*
*Type help for help.*
*
*
Where do I find the mentioned article?
*
*
2. When running a test program, sourced from Core Java Volume 2 (7th
Edition), the following error is being displayed:
*java.sql.SQLException: No suitable driver found for
jdbc:postgresql:COREJAVA;cre*
*ate=true*
*at java.sql.DriverManager.getConnection(DriverManager.java:602)*
*at java.sql.DriverManager.getConnection(DriverManager.java:185)*
*at TestDB.getConnection(TestDB.java:82)*
*at TestDB.runTest(TestDB.java:43)*
*at TestDB.main(TestDB.java:20)*
*
*
In order to enable JDK to connect to the library files, I copied over *
postgresql-8.4-702.jdbc3.jar*, and,
*postgresql-8.4-702.jdbc4.jar* to *C:\Program Files\Java\jre6\lib\ext*.
It was assumed that the file naming indicates type 3 and type 4
respectively, and, I was intending to use type 4, since it is more efficient
to use a library that translates Java to the database language for Postgre:
*postgresql-8.4-702.jdbc3.jar*
A type 3 driver is a pure Java client library that uses a
database-independent protocol to communicate database requests to a server
component, which then translates the requests into a database-specific
protocol. This can simplify deployment since the database-dependent code is
located only on the server.
*postgresql-8.4-702.jdbc4.jar*
A type 4 driver is a pure Java library that translates JDBC requests
directly to a database-specific protocol.
*TestDB.java*
/**
@version 1.01 2004-09-24
@author Cay Horstmann (all rights reserved)
*/
import java.sql.*;
import java.io.*;
import java.util.*;
/**
This program tests that the database and the JDBC
driver are correctly configured.
*/
class TestDB
{
public static void main (String args[])
{
try
{
runTest();
}
catch (SQLException ex)
{
while (ex != null)
{
ex.printStackTrace();
ex = ex.getNextException();
}
}
catch (IOException ex)
{
ex.printStackTrace();
}
}
/**
Runs a test by creating a table, adding a value, showing the table
contents, and
removing the table.
*/
public static void runTest()
throws SQLException, IOException
{
Connection conn = getConnection();
try
{
Statement stat = conn.createStatement();
stat.execute(CREATE TABLE Greetings (Message CHAR(20)));
stat.execute(INSERT INTO Greetings VALUES ('Hello, World!'));
ResultSet result = stat.executeQuery(SELECT * FROM Greetings);
result.next();
System.out.println(result.getString(1));
stat.execute(DROP TABLE Greetings);
}
finally
{
conn.close();
}
}
/**
Gets a connection from the properties specified
in the file database.properties
@return the database connection
*/
public static Connection getConnection()
throws SQLException, IOException
{
Properties props = new Properties();
FileInputStream in = new FileInputStream(database.properties);
props.load(in);
in.close();
String drivers = props.getProperty(jdbc.drivers);
if (drivers != null)
System.setProperty(jdbc.drivers, drivers);
String url = props.getProperty(jdbc.url);
String username = props.getProperty(jdbc.username);
String password = props.getProperty(jdbc.password);
return DriverManager.getConnection(url, username, password);
}
}
*database.properties*
dbc.drivers=org.postgresql.Driver
jdbc.url=jdbc:postgresql:COREJAVA;create=true
jdbc.username=postgre
jdbc.password=
*command line*
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Jonecho %CLASSPATH%
.;.;C:\PROGRA~1\JMF21~1.1E\lib\sound.jar;C:\PROGRA~1\JMF21~1.1E\lib\jmf.jar;C:\P
ROGRA~1\JMF21~1.1E\lib;C:\Program
Files\Java\external_jars\junit4.9b2\junit4.9b2
\junit-4.9b2.jar;*C:\Program Files\PostgreSQL\pgJDBC;*
C:\Documents and Settings\Joncd c:\Program Files\PostgreSQL\pgJDBC
C:\Program Files\PostgreSQL\pgJDBCdir
Volume in drive C has no label.
Volume Serial Number is D80F-8634
Directory of C:\Program Files\PostgreSQL\pgJDBC
09/07/2011 15:42DIR .
09/07/2011 15:42DIR ..
03/04/2011 23:22 502,118 postgresql-8.4-702.jdbc3.jar
03/04/2011 23:22 539,510 *postgresql-8.4-702.jdbc4.jar*
09/07/2011 15:42DIR scripts
09/07/2011 15:42 5,759,102 uninstall-pgjdbc.exe
3 File(s) 6,800,730 bytes
3 Dir(s)
[GENERAL] Hot standby on Windows
Hello all, I am having a little trouble with a hot standby configuration on Windows, and I was hoping I could find an answer here. Everything works fine (both machines run just fine, and the hot standby works like a charm), except for one problem. The pg_archivecleanup refuses to run; it seems like I am writing the wrong command, since the %r does not get replaced. The log is full of sets of lines like these : pg_archivecleanup: must specify restartfilename Try pg_archivecleanup --help for more information. 2011-07-09 11:09:28 EEST WARNING: archive_cleanup_command pg_archivecleanup.exe C:\ArchiveDir\ %r c:\cleanup.log: return code 2 I thought that the command line would be built properly (I mean the %r would be replaced by the caller), but for some reason it is not happening. I'm out of ideas to try... what am I doing wrong ? Thanks in advance for any help. Stefan.
[GENERAL] For a LAPP setup what is better: 1 fast or 2 slower machines
Hello, I'm running a CentOS 5.6 / 64 bit Linux with PostgreSQL 8.4.8 and Drupal 7.4 on a 4GB quad-CPU machine. In Autumn I can change my hoster and for EUR 100 can either take 1) 2 machines with i7-920 Quad-Core 8 GB RAM, 2 x 750 GB SATA-II HDD (Software-RAID 1) or 2) 1 machine i7-980X Hexa-Core 24 GB RAM, 1 x 1,5 TB SATA II What would you prefer? I know, the usual answer is you don't provide enough details, but would it really help anyone if I provide all the details and print here all my scripts? My current setup - postgresql.conf: max_connections = 50 shared_buffers = 1024MB and unix pipe only (for 2 machines I will have to switch to TCP at 100 Mb/s switch). I use pgbouncer with pool_mode = session server_reset_query = DISCARD ALL; server_check_delay = 10 max_client_conn = 200 default_pool_size = 16 and at peak times it reports: 70 req/s, in 11616 b/s, out 784748 b/s,query 89556 us 34 req/s, in 5484 b/s, out 176943 b/s,query 60871 us 45 req/s, in 17976 b/s, out 169849 b/s,query 66496 us 53 req/s, in 10961 b/s, out 648585 b/s,query 40546 us My httpd.conf: IfModule prefork.c StartServers 8 MinSpareServers5 MaxSpareServers 20 ServerLimit 120 MaxClients 120 MaxRequestsPerChild 4000 /IfModule Thank you Alex -- 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] No suitable driver found for jdbc:postgresql [error]
On 07/09/2011 12:56 PM, Jonathan Camilleri wrote: /Notes for Windows users/ http://www.postgresql.org/docs/9.0/static/app-psql.html
Re: [GENERAL] For a LAPP setup what is better: 1 fast or 2 slower machines
On Sat, Jul 9, 2011 at 10:19 AM, Alexander Farber alexander.far...@gmail.com wrote: Hello, I'm running a CentOS 5.6 / 64 bit Linux with PostgreSQL 8.4.8 and Drupal 7.4 on a 4GB quad-CPU machine. In Autumn I can change my hoster and for EUR 100 can either take 1) 2 machines with i7-920 Quad-Core 8 GB RAM, 2 x 750 GB SATA-II HDD (Software-RAID 1) or 2) 1 machine i7-980X Hexa-Core 24 GB RAM, 1 x 1,5 TB SATA II What would you prefer? 2 machines for two reasons. 1: separating out app from db server means it's way easier to figure out which is acting up should you have a performance problem. 2: the dual machines have RAID-1 hard drives, the single machine just has one big 1.5TB drive. No way would I run a production system on a single drive, especially considering how cheap hard drives are nowadays. -- 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] For a LAPP setup what is better: 1 fast or 2 slower machines
Hi, On Sat, Jul 9, 2011 at 6:53 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Sat, Jul 9, 2011 at 10:19 AM, Alexander Farber alexander.far...@gmail.com wrote: 1) 2 machines with i7-920 Quad-Core 8 GB RAM, 2 x 750 GB SATA-II HDD (Software-RAID 1) 2) 1 machine i7-980X Hexa-Core 24 GB RAM, 1 x 1,5 TB SATA II 2 machines for two reasons. 1: separating out app from db server means it's way easier to figure out which is acting up should you have a performance problem. 2: the dual machines have RAID-1 hard drives, the single machine just has one big 1.5TB drive. No way would I run a production system on a single drive, especially considering how cheap hard drives are nowadays. and where to run the pgbouncer, probably on the Apache machine? Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
Jeff Davis wrote: On Fri, 2011-07-08 at 23:39 -0700, Darren Duncan wrote: What if you used the context of the calling code and resolve in favor of whatever match is closest to it? The problem is related to general-purpose programming languages. Basically start looking in the lexical context for an x and if you find one use that; otherwise, assuming we're talking about referencing code that lives in the database such as a function, look at the innermost schema containing the referencing code and see if it has a direct child named x; otherwise go up one level to a parent schema, and so on until you get to the top, and finding none by then say it doesn't exist. This is an example of where data languages and normal programming languages have a crucial difference. With a data language, you have this problem: 1. An application uses a query referencing 'y.z.foo' that resolves to internal object with fully-qualified name 'x.y.z'. 2. An administrator creates object 'y.z.foo'. Now, the application breaks all of a sudden. In a normal prgramming language, if the schema of the two foos are different, the compiler could probably catch the error. SQL really has no hope of catching it though. PostgreSQL has this problem now in a couple ways, but it's much easier to grasp what you might be conflicting with. If you have multiple nested levels to traverse and different queries using different levels of qualification, it gets a little more messy and I think a mistake is more likely. Well, my search path suggestion was based on Tom Lane's comment that the SQL spec requires us to be able to [support abbreviations] and I expected it would be syntactically and semantically backwards compatible with how things work now. FYI, with Muldis D, being more green fields, there are no search paths in the general case, and every entity reference is unambiguous because it has to be fully-qualified. However, I also support relative references, and in fact require their use for references within the same database, which carries a number of benefits, at the cost of being a few characters more verbose than when using a search path. So introducing new things with the same names in different namespaces won't break anything there, even if they are closer. Its essentially like navigating a Unix filesystem but with . rather than /. So for example, if you had 2 sibling schemas s1 and s2, each with 2 functions f1,f2 and a table t, then s1.f1 would reference s1.f2 and s1.t as sch.lib.f2 and sch.data.t respectively, while s1.f1 would refer to the entities in s2 as sch.par.s2.lib.f1 and sch.par.s2.data.t and such (a function can also refer to itself anonymously as rtn if it's recursive). The sch is like . in Unix and the par is like .. in Unix. The data is for data tables or views (and cat is for catalog tables/views) while lib is for user-defined types, routines, constraints, etc (and sys is for built-in types and routines, but sys may be omitted and search paths exist just for built-ins). Synonyms are also supported. I don't expect you would adopt relative (fully-qualified) references, because the syntax isn't in standard SQL (I think), but I did. Unless you like them and can come up with a syntax that will fit into how SQL does things. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
