[GENERAL] pgsql2shp usage
Hello to all, I downloaded an OSM map file namely india.osm.bz2. I transported it in PostgreSQL database gis with the command : ./osm2pgsql -m -d gis india.osm.bz2 The above command resulted in the creation of the following tables filled with data in database gis gis=# \d List of relations Schema |Name| Type | Owner ++--+-- public | geometry_columns | table| anisha public | planet_osm_line| table| anisha public | planet_osm_point | table| anisha public | planet_osm_polygon | table| anisha public | planet_osm_roads | table| anisha public | polygon_oid_seq| sequence | postgres public | spatial_ref_sys| table| anisha Now to convert the map data of one of the above tables in a shape file, I issued the following command: pgsql2shp -f myfile gis planet_osm_line Output: Initializing... Warning, field addr:housenumber renamed to ADDR:HOUSE Warning, field addr:interpolation renamed to ADDR:INTER Warning, field admin_level renamed to ADMIN_LEVE Warning, field construction renamed to CONSTRUCTI Warning, field power_source renamed to POWER_SOUR Done (postgis major version: 1). Output shape: PolyLine Dumping: XXX [126149 rows]. Question : ___ How should I verify that the shape file resultant of the above command is a valid and uncorrupted one ? ___ I am worried because the projection file namely myfile.prj contains the following ! PROJCS[WGS84 / Simple Mercator,GEOGCS[WGS 84,DATUM[WGS_1984,SPHEROID[WGS_1984, 6378137.0, 298.257223563]],PRIMEM[Greenwich, 0.0],UNIT[degree, 0.017453292519943295],AXIS[Longitude, EAST],AXIS[Latitude, NORTH]],PROJECTION[Mercator_1SP_Google],PARAMETER[latitude_of_origin, 0.0],PARAMETER[central_meridian, 0.0],PARAMETER[scale_factor, 1.0],PARAMETER[false_easting, 0.0],PARAMETER[false_northing, 0.0],UNIT[m, 1.0],AXIS[x, EAST],AXIS[y, NORTH],AUTHORITY[EPSG,900913]] The latitude/longitude values don't seem to be of India Thanking you in anticipation, Regards, Anisha Kaul - Hi-Tech Gears Limited, Gurgaon, India -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Python flattens composite types to string?
Hi, I recently wrote PL/Python code that worked on fields of composite types. The plpy.execute() command on a SELECT returns a list of nice dictionaries keyed on field names, containing the fields. For numeric types, the type of the dictionary values are as expected. To my chagrin however, if a field contains a composite type, it is flattened to a string. I would have expected a composite type field to be returned as a dictionary of values of the proper types, keyed on the names of the elements of the composite type. I was able to work around this problem in an ugly way, but I can imaging cases where this would render plpy unsuitable. I see nothing in the documentation about this http://www.postgresql.org/docs/8.4/static/plpython.html It only talks about passing composite types into and out of functions. Is this a bug? This is psql v. 8.1.18 on one machine, and 8.4.1 on another. Am I somehow doing it wrong? -- See attached test Cheers! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | e-Science / AstroGrid-D Zi. 35 Bg. 20 | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- vim:set filetype=pgsql: /** Illustrates PL/Py flattening of composite types * Log in as user 'postgres' in directory containing this file, start psql, then create language plpythonu;-- if haven't done already \i pycomptype.sql select pycomptypes.color_read( 1 ); * To get rid of the tables drop schema pycomptypes cascade; */ CREATE SCHEMA pycomptypes AUTHORIZATION postgres; SET search_path TO pycomptypes; CREATE TYPE value_sigma AS ( value DOUBLE PRECISION, sigma DOUBLE PRECISION ); CREATE TABLE colors ( colors_id INTEGER PRIMARY KEY, red value_sigma, green value_sigma, bluevalue_sigma ); INSERT INTO colors VALUES ( 1, (1.21, 0.05), (1.45, 0.06), (1.83, 0.07) ); INSERT INTO colors VALUES ( 2, (0.94, 0.05), (0.38, 0.03), (1.81, 0.07) ); INSERT INTO colors VALUES ( 3, (0.56, 0.02), (0.74, 0.05), (1.90, 0.08) ); CREATE OR REPLACE FUNCTION color_read( color_id INTEGER ) RETURNS VOID AS $$ cmd = 'SELECT red,green,blue FROM colors WHERE colors_id=' + str( color_id ) for t in plpy.execute( cmd ): plpy.notice( 'type of color item: %s' % ( type( t['red'] ) ) ) $$ LANGUAGE PLPYTHONU; GRANT SELECT ON colors TO PUBLIC; GRANT USAGE ON SCHEMA pycomptypes TO PUBLIC; -- 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] pgadmin save password
glaucomag escribió: Hi, I've a problem with pgadmin. If I access to database with user X and I save password, when I access to database from shell (psql) password is not required. Of course pg_hba.conf is: local database X md5 If I don't save password in pgadmin, it's ok (psql required password). The question is this: is it possible that pgadmin save password also for psql? And where pgadmin save password? Thank you pgAdmin saves passwords in ~/.pgpass http://www.pgadmin.org/docs/1.4/pg/libpq-pgpass.html Best, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6) AntiVirus: ClamAV 0.95.2/10275 - Sat Jan 9 02:06:46 2010 by Markus Madlener @ http://www.copfilter.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] Table appears on listing but can't drop it
On Fri, Jan 8, 2010 at 17:44, Sam Mason s...@samason.me.uk wrote: There's an extra space at the beginning of the skynet line, could it Is there a particular reason space is allowed in name identifiers? I see nothing but confusion if a space exists. -- - Rikard - http://bos.hack.org/cv/ -- 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] set-level update fails with unique constraint violation
Tom Lane wrote: Daniel Verite dan...@manitou-mail.org writes: But still I wonder why there is that difference in behavior between NON DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint doesn't get deferred by using SET CONSTRAINTS. In the first case, we get the after each row behavior with the pk=pk+1 failure, as with the previous PG versions. In the second case, we get the after each statement behavior which I believe complies with the standard, contrary to the first case, and successfully achieves the pk=pk+1 update as expected. Personally, I would have imagined that behavior #1 would be removed once behavior #2 was implemented, not that the two would co-exist. Is there a reason to keep #1? 1. Performance. The cost of #2 is very large, and the number of cases where you actually need it is not. Per Dean's explanation upthread, It looks like an additional cost for #2 would occur mostly when temporary conflicts occur, that is, when it's needed. I've tried UPDATEs of a primary key in batches of 1M rows with 8.5, and in the general case of no conflict, I get #2 being about 8-15% slower than #1. I've seen no difference for INSERTs. When there are temporary conflicts, #2 is slower but succeeds whereas #1 fails, so #2 is the winner. When there are persistant conflicts, #2 fails slower than #1, but do we really care? 2. Backwards compatibility. Some apps might be depending on the details of the behavior. Apparently, the occurrence of conflicts during the execution is mostly unpredictable anyway, from the point of view of the end user. For example I was under the illusion that UPDATE...SET pk=pk-1 always worked, but I've discovered while testing that it wasn't the case. Conversely depending on it to fail, for this update or a similar update, thats seems insane for an app. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.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] Server name in psql prompt
Mark Morgan Lloyd wrote: I thought earlier that I could use finger as a hack for querying the server, i.e. I could put e.g. a disc set name in /home/postgres/.plan. However I then realised that I'd need %M to be expanded before %`, so that I could do something like \set PROMPT1 '`finger postg...@%m|filter`: %/%R%# ' where filter only returned the bit that was needed. I've not tried this due to the ordering issue. I've just been playing with this. I can finger a fixed server for its true name using e.g. postgres: markMLl= \set PROMPT1 '%`finger postg...@postgres|head -1|cut -d \[ -f 2|cut -d . -f 1`: %/%R%# ' postgres1: markMLl= but if I try to replace the fixed server name in that \set PROMPT1 '%`finger postg...@%:HOST:|head... the nested variable doesn't get expanded. That's obviously useful if there's only one server or the servers are pooled since it means that the current disk set or whatever can be identified, but less so if there are multiple servers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] pgsql2shp usage
The Attribute Tables for a shape file is stored in .DBF format which has a restriction that field names are only ten characters long, so the warnings are simply informing you that those field names that are longer than 10 characters are being truncated. The projection definition indicates that the output spatial reference system (900913) is a variation of the Mercator projection which is used by Google Maps, Bing Maps, and other web map services. So, these are not lat/long coordinates. Randall Thompson Castle Rock GIS randall.thomp...@castlerockgis.com == Hello to all, I downloaded an OSM map file namely india.osm.bz2. I transported it in PostgreSQL database gis with the command : ./osm2pgsql -m -d gis india.osm.bz2 The above command resulted in the creation of the following tables filled with data in database gis gis=# \d List of relations Schema |Name| Type | Owner ++--+-- public | geometry_columns | table| anisha public | planet_osm_line| table| anisha public | planet_osm_point | table| anisha public | planet_osm_polygon | table| anisha public | planet_osm_roads | table| anisha public | polygon_oid_seq| sequence | postgres public | spatial_ref_sys| table| anisha Now to convert the map data of one of the above tables in a shape file, I issued the following command: pgsql2shp -f myfile gis planet_osm_line Output: Initializing... Warning, field addr:housenumber renamed to ADDR:HOUSE Warning, field addr:interpolation renamed to ADDR:INTER Warning, field admin_level renamed to ADMIN_LEVE Warning, field construction renamed to CONSTRUCTI Warning, field power_source renamed to POWER_SOUR Done (postgis major version: 1). Output shape: PolyLine Dumping: XXX [126149 rows]. Question : ___ How should I verify that the shape file resultant of the above command is a valid and uncorrupted one ? ___ I am worried because the projection file namely myfile.prj contains the following ! PROJCS[WGS84 / Simple Mercator,GEOGCS[WGS 84,DATUM[WGS_1984,SPHEROID[WGS_1984, 6378137.0, 298.257223563]],PRIMEM[Greenwich, 0.0],UNIT[degree, 0.017453292519943295],AXIS[Longitude, EAST],AXIS[Latitude, NORTH]],PROJECTION[Mercator_1SP_Google],PARAMETER[latitude_of_origin, 0.0],PARAMETER[central_meridian, 0.0],PARAMETER[scale_factor, 1.0],PARAMETER[false_easting, 0.0],PARAMETER[false_northing, 0.0],UNIT[m, 1.0],AXIS[x, EAST],AXIS[y, NORTH],AUTHORITY[EPSG,900913]] The latitude/longitude values don't seem to be of India Thanking you in anticipation, Regards, Anisha Kaul - Hi-Tech Gears Limited, Gurgaon, India
Re: [GENERAL] WAL Log Shipping - Warm Standby not working under 8.3.7
We are still on PG 8.3.7 and I don't see pg_standby. It looks like it was
added with 8.3.8. I will put in a request to update to 8.3.9 as a part of our
next software upgrade (internal product we write) but for now I need to try to
figure out why this isn't working under 8.3.7. I did manage to bring up
replication under 8.3.7 in the test environment just fine, so the question is
now why the standby PG instance won't come up in recovery mode and why it is
looking so far back for the next WAL log file.
-K
On 1/8/10 5:55 PM, Erik Jones ejo...@engineyard.com wrote:
On Jan 8, 2010, at 4:50 PM, Erik Jones wrote:
On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
OK,
So what am I doing wrong here?
Installed PG 8.3.7 on Slave machine
Restored from last evening's backup from the master DB to make the rsync
across the network finish sooner.
Shut down the PG instance on the slave machine
Ran a script that does the following:
select pg_start_backup('Master_Backup');
rsync -rvlpogtz ${masterdb}/* ${slave_dbus...@${slave_host}:${slavedb}
select pg_stop_backup();
ssh ${slave_dbus...@${slave_host} rm ${slavedb}/postmaster.pid 2/dev/null
ssh ${slave_dbus...@${slave_host} rm ${slave_backup_path}/0* 2/dev/null
ssh ${slave_dbus...@${slave_host} ${PSQL_BIN}/pg_ctl -D ${slavedb} -l
logfile start
Is ${slave_backup_path} your archive directory? Why are you deleting all of
you archives there?
Also, what are the contents of your recovery.conf file? Are you using
pg_standby? The typical setup is to clear datadir/pg_xlog on your standby
and use pg_standby to recovery files directly from your archive directory?
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
Re: [GENERAL] set-level update fails with unique constraint violation
Daniel Verite dan...@manitou-mail.org writes: Tom Lane wrote: 1. Performance. The cost of #2 is very large, and the number of cases where you actually need it is not. Per Dean's explanation upthread, It looks like an additional cost for #2 would occur mostly when temporary conflicts occur, that is, when it's needed. I'm not sure where you got that from his explanation, but it's not the case. The problem with any type of delayed verification is that it requires a second index search, on top of the one you already did while making your index entry. This occurs whether or not there is any conflict. The problem is especially acute when you have an update or insert affecting a large fraction of the table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: sid data date 11.1 2009-09-01 1:00:00 12.1 2010-01-01 1:00:20 23.1 2009-09-01 1:00:10 20.1 2010-01-01 1:00:30 I create index for data field. Each sid may have millions of rows. I want to get maximum data value and corresponding time for each group of sid. Here is my query: select t1.sid , max(t1.data) (select t2.date from test t2, where t2.sid = t1.sid and t2.date between '2009-08-01' and '2010-01-02' and order by t2.data DESC limit 1 ) from test t1 where t1.date between '2009-08-01' and '2010-01-08' and group by t1.sid ## But when max() in postgresql may slow down the search when there are millions of rows for each sid. So I use order by t2.data DESC limit 1 to find max: select t1.sid , (select t2.data from test t2, where t2.sid = t1.sid and t2.date between '2009-08-01' and '2010-01-02' and order by t2.data DESC limit 1 ) (select t2.date from test t2, where t2.sid = t1.sid and t2.date between '2009-08-01' and '2010-01-02' and order by t2.data DESC limit 1 ) from test t1 where t1.date between '2009-08-01' and '2010-01-08' and group by t1.sid ## The second query looks strange since similar search is done twice. Because of two fields, the following can not be used directly in the above query. (select t2.date, t2.data from test t2, where t2.sid = t1.sid and t2.date between '2009-08-01' and '2010-01-02' and order by t2.data DESC limit 1 ) Any suggestions for the best way to get maximum data value and corresponding time for each group of sid in my case? Thanks a lot. ouyang
Re: [GENERAL] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
On 09/01/2010 16:43, zxo102 ouyang wrote: Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: Without meaning to sound unhelpful, why on earth are you using a beta version when 8.3 was released *ages* ago and has had several bug-fix updates since? I'd look first in the release notes for the updates to see if there's anything that addresses your problem, and then upgrade to the latest release version at the first opportunity. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Table appears on listing but can't drop it
Rikard Bosnjakovic rikard.bosnjako...@gmail.com writes: Is there a particular reason space is allowed in name identifiers? I see nothing but confusion if a space exists. The SQL standard requires that double-quoted identifiers be allowed to contain anything. regards, tom lane -- 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] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
zxo102 ouyang zxo...@gmail.com wrote: Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: I'm guessing you mean 8.4-beta3, right? Any suggestions for the best way to get maximum data value and corresponding time for each group of sid in my case? Based on your data: test=*# select * from test; sid | data |date -+--+- 1 | 1.1 | 2009-09-01 01:00:00 1 | 2.1 | 2010-01-01 01:00:20 2 | 3.1 | 2009-09-01 01:00:10 2 | 0.1 | 2010-01-01 01:00:30 (4 Zeilen) Try: Zeit: 0,227 ms test=*# select distinct on (sid) sid, data, date from test order by sid, data desc, date; sid | data |date -+--+- 1 | 2.1 | 2010-01-01 01:00:20 2 | 3.1 | 2009-09-01 01:00:10 (2 Zeilen) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
Andreas Kretschmer wrote: zxo102 ouyang zxo...@gmail.com wrote: Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: I'm guessing you mean 8.4-beta3, right? either of those are unsuitable for any kind of production use... Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
I just upgraded my home gentoo system's postgresql from 8.2.14 to 8.4.2. I use it mostly for fooling around and keeping smatterings of personal data, so it was simple laziness which kept me from upgrading sooner, triggered by the gentoo switch back in 8.2.mumble in how they manage postgresql. Everything went smoothly except the permissions of the directory /var/run/postgresql with the domain socket .s.PGSQL.5432. This dir had permissions of 770, owned by postgres.postgres, so no mere mortals could access it. I have changed this to 775 and can now access it. Didn't 8.2 put these in /tmp? Maybe this was a gentoo thing. What should the permissions be for this? Or does gentoo do their own thing and there is a different standard way of handling this? -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room 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] Table appears on listing but can't drop it
On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote: postgres=# drop database skynet; ERROR: database skynet does not exist do: psql -l | hexump -C and examine output. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Andreas Kretschmer wrote: zxo102 ouyang zxo...@gmail.com wrote: Hi everyone,I am using postgresql 8.3-beta3. I have a table 'test' with three fields: I'm guessing you mean 8.4-beta3, right? either of those are unsuitable for any kind of production use... Hey, we needs beta-testers, right? And yes, read again, the table is called 'test' ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] dynamic insert in plpgsql
Grzegorz Jaśkiewicz gryz...@gmail.com writes: Is there any nice way to do something like that in plpgsql: EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||''; See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers -- dim -- 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] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
On Sat, Jan 9, 2010 at 2:20 PM, fe...@crowfix.com wrote: I just upgraded my home gentoo system's postgresql from 8.2.14 to 8.4.2. I use it mostly for fooling around and keeping smatterings of personal data, so it was simple laziness which kept me from upgrading sooner, triggered by the gentoo switch back in 8.2.mumble in how they manage postgresql. Everything went smoothly except the permissions of the directory /var/run/postgresql with the domain socket .s.PGSQL.5432. This dir had permissions of 770, owned by postgres.postgres, so no mere mortals could access it. I have changed this to 775 and can now access it. Didn't 8.2 put these in /tmp? Maybe this was a gentoo thing. What should the permissions be for this? Or does gentoo do their own thing and there is a different standard way of handling this? This sounds like a Gentoo thing. The location of all the various pg files is a compile time option and lots of packagers make different decisions based on their distro layouts. Ubuntu / Debian for instance puts all the postgresql.conf type files in /etc/postgresql/8.x/clustername/ and allows you to have multiple instances of different versions by moving things around from the default of a single pg install from source. -- 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] An issue with max() and order by ... limit 1 in postgresql8.3-beta3
On Sat, Jan 9, 2010 at 2:46 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Andreas Kretschmer wrote: zxo102 ouyang zxo...@gmail.com wrote: Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: I'm guessing you mean 8.4-beta3, right? either of those are unsuitable for any kind of production use... Hey, we needs beta-testers, right? And yes, read again, the table is called 'test' ... True, but if you're gonna test betas / alphas, I'd think 8.5 alpha would be the choice for testing. 8.4's beta ended quite some time ago. -- 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] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
On Saturday 9. January 2010 22.20.36 fe...@crowfix.com wrote: I just upgraded my home gentoo system's postgresql from 8.2.14 to 8.4.2. I use it mostly for fooling around and keeping smatterings of personal data, so it was simple laziness which kept me from upgrading sooner, triggered by the gentoo switch back in 8.2.mumble in how they manage postgresql. Everything went smoothly except the permissions of the directory /var/run/postgresql with the domain socket .s.PGSQL.5432. This dir had permissions of 770, owned by postgres.postgres, so no mere mortals could access it. I have changed this to 775 and can now access it. Didn't 8.2 put these in /tmp? Maybe this was a gentoo thing. What should the permissions be for this? Or does gentoo do their own thing and there is a different standard way of handling this? In Gentoo, you must add the database owner (probably your own username) to the group Postgres. This was changed with 8.3, and if you had read the message from emerge, you should have noticed. And, yes, I fumbled a lot with this myself. regards, -- Leif Biberg Kristensen http://solumslekt.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] dynamic insert in plpgsql
2010/1/9 Dimitri Fontaine dfonta...@hi-media.com: Grzegorz Jaśkiewicz gryz...@gmail.com writes: Is there any nice way to do something like that in plpgsql: EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||''; See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers -- that approach is awful. Sam's method (or the one I posted in the same thread using dollar quoting) is much faster/better. merlin -- 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] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
On Sat, Jan 09, 2010 at 11:18:19PM +0100, Leif Biberg Kristensen wrote: In Gentoo, you must add the database owner (probably your own username) to the group Postgres. This was changed with 8.3, and if you had read the message from emerge, you should have noticed. And, yes, I fumbled a lot with this myself. Ahh ... I skipped 8.3 and didn't see that message. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room 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] Gentoo, 8,2 --- 8.4, and /var/run/postgresql in mode 770
fe...@crowfix.com wrote: Everything went smoothly except the permissions of the directory /var/run/postgresql with the domain socket .s.PGSQL.5432. This dir had permissions of 770, owned by postgres.postgres, so no mere mortals could access it. I have changed this to 775 and can now access it. Didn't 8.2 put these in /tmp? Maybe this was a gentoo thing. http://bugs.gentoo.org/236546 talks about what changed and shows the associated warning, which I'm guessing you didn't see. You can move it back to /tmp if you want. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] dynamic insert in plpgsql
This is what I hacked quickly last night, what you guys think? CREATE OR REPLACE FUNCTION something.ziew_partition_insert() RETURNS TRIGGER AS $_$ DECLARE partition_table_name varchar; old_partition_table_name varchar; BEGIN SELECT 'something_partitions.ziew_'||to_char(NEW.logtime, 'IYYY_MM') INTO partition_table_name; BEGIN EXECUTE 'INSERT INTO '||partition_table_name||' (SELECT (something.ziew '||quote_literal(NEW)||').*)'; EXCEPTION WHEN undefined_table THEN BEGIN SET client_min_messages = error; EXECUTE 'CREATE TABLE '||partition_table_name||'() INHERITS (something.ziew)'; EXECUTE 'ALTER TABLE '||partition_table_name||' ADD PRIMARY KEY (id)'; EXECUTE 'CREATE INDEX something_time'|| to_char(NEW.logtime, 'IYYY_MM')||' ON '||partition_table_name||'(logtime)'; SELECT 'something_partitions.ziew_'||to_char(NEW.logtime-'2 months'::interval, 'IYYY_MM') INTO old_partition_table_name; -- don't care if it fails BEGIN EXECUTE 'DROP TABLE '||old_partition_table_name; EXCEPTION WHEN others THEN --- in place for NOP old_partition_table_name := ''; END; EXECUTE 'INSERT INTO '||partition_table_name||' (SELECT (something.ziew '||quote_literal(NEW)||').*)'; EXCEPTION WHEN others THEN RAISE EXCEPTION 'somethings wrong %',SQLERRM; RETURN NULL; END; END; RETURN NULL; END; $_$ LANGUAGE 'plpgsql'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] aggregate over tables in different schema
I've tables in different schemas all with the same name and structure. I'd like to compute an aggregate on the union of those tables. I don't know the schemas in advance. The list of the schema will be built selecting all the schemas that contain a table with that name. Other than building dynamically the statement as a list of union or building up a view is there any other way? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
