Re: [GENERAL] PLPGSQL SETOF functions
1) If you declare a return type setof TABLENAME the resultset will contain rows with field definitions like the table. 2) To call the function from another plpgsql function use: declare row record begin for row in select * from dates_pkg.getbusinessdays(...) Loop ...process... end loop ... end see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING On 06/28/2011 09:34 PM, David Greco wrote: I am porting some Oracle code to PLPGSQL and am having a problem with functions that return SETOF datatype. In Oracle, the functions I'm porting return a TABLE of TYPE datatype, this TABLE being itself a named type. I am not aware of how to do this in PLPGSQL. Consider a function with header: CREATE OR REPLACE FUNCTION dates_pkg.getbusinessdays(pstartdate timestamp with time zone, penddate timestamp with time zone) RETURNS SETOF timestamp with time zone AS I can easily call this function in SQL like so: select * from dates_pkg.getbusinessdays( now(), now() + INTERVAL '7' day ) as business_day; However, I can't figure out how to call this function from another plpgsql function. Any hints? ~Dave Greco
Re: [GENERAL] Multi-tenancy in Postgres
On Tue, 28 Jun 2011 17:04:54 -0600, Rob Sargent wrote: On 06/28/2011 04:52 PM, Greg Smith wrote: On 06/28/2011 05:45 PM, Rob Sargent wrote: I think Greg might be forgetting that some of us don't always get to choose what we work on. I was in a shop that decided to go with multi-tenancy for reason both technical and um, er envious. There are certainly successful deployments of multi-tenant PostgreSQL out there, ones that make sense. What I was trying to communicate is that the particular variation proposed by this academic paper doesn't seem the right direction for PostgreSQL development to head in to me. This project is stubborn about resolving the problems people actually have, and the ones the paper tries to solve are not the ones I've seen in my own experiments in multi-tenant deployments. Yes, your point is well taken here, and that wasn't even hinted at in my previous (top! oops) post. My point was that hacks in the field (i.e. me) will have to do multi-tenancy on postgres and though this implementation may not become the answer, any leg up would be appreciated. I think this may be quite interesting solution. Actually I created such approach, for many reasons, but it's hard-coded, I mean in any place when query is executed I add this tenancy id, I called it differently, and it works perfectly. But such feature will not grow quite fast until PostgreSQL ecosystem will not grow, for example I see problems with Java + Hibernate + Caching, when tenancy id will be hidden, actually You may query for two different objects with same id, if we will allow dynamic tanacy switch (should be done, as You will loose connection pool benefits). Regards, Radek -- 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] rationale behind quotes for camel case?
Le mardi 28 juin 2011 à 11:09 -0500, dennis jenkins a écrit : Any suggestions on how to name tables when table names contain both multi-word nouns and mutli-table many-many mappings? [..] The real table names are normal English words with subjective meaning. Not sure what you mean by suggestive meaning? are the tables called things like 'cool' and 'uncool', for instance? I like to keep things clear, so for instance to record customers and what services they subscribe to, I'd write : create table customer (id_customer serial, libelle text); create table service (id_service serial, libelle text); create table customer_service (id_customer integer, id_service integer); + foreign key constraints, obviously. It's not always simple to find appropriate names. But I take the time, because it makes it *much* easier for me when I write/debug the SQL in my Perl modules. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Real type with zero
Hello, how I can tell my postgresql to store last zero of real type ? I put value 2.30 and when I select that column i see 2.3 without zero. -- Regards, Condor -- 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] Real type with zero
what you probably looking for is formatting the output into a string. Postgresql will store it as 2.3, because that is what 2.30 is anyway. Its up to you to format it before passing it on to the user/business logic/whatever. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LOCK TABLE permission requirements
I've been looking around in the 9.0 documentation, but couldn't find the permission requirements for LOCK TABLE (in particular, LOCK TABLE IN SHARE MODE). From the source, you need at least one of UPDATE, DELETE or TRUNCATE. Is there a reason why the INSERT privilege is not sufficient for LOCK TABLE, or is this just an oversight? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Multi-tenancy in Postgres
Thank you so far for your perspectives on this. I especially agree some of the things raised by Radoslaw and Rob. While it may not be common to come across a scenario where this type of approach fits, I would like to point out that this type of solution is built into some commercial DBMS solutions already (SQL Azurehttp://blogs.msdn.com/b/cbiyikoglu/archive/2011/03/23/moving-to-multi-tenant-model-made-easy-with-sql-azure-federations.aspxand Progress http://web.progress.com/en/openedge/openedge.html). We also see application-level solutions for this today (Tungsten's replicator and Grails' Multi-Tenanthttp://multi-tenant.github.com/grails-multi-tenant-core/guide/1.%20Overview.htmlplugin and many others). I do think some of what's being done at the application layer today should be handled by the DB - as Rob says, any leg-up would be appreciated). If it was only for the likes of SalesForce then there wouldn't be in-db and application-level solutions being created to solve this. I made the original post because I too am searching for ways to achieve this with more assistance from the DB (currently looking at Postgres SCHEMAs for per-customer separation and table inheritance to achieve something akin to a 'shared table' that can be extended to suit each customers customisations). Sure, I could have one database per-customer but that creates overhead (which the paper goes into about). Improving the overhead per-instance would help address some of this and is very welcome but even then, if I have one application and thousands of databases in a cluster then I'd have to alter each schema when I update the application - and deal with the error conditions that could arise. Hosting companies may want better isolation for each customer, but SaaS applications operate on a different level where they control the database and the queries that execute on them so the same level of isolation may not be needed. Also, if we look to the NoSQL world, one of the often-touted benefits there is for schema-less systems. These aren't always chosen solely because people don't need a schema-less system, but because they need to, as an example, store different types of data per-customer. The paper in my original mail goes some way to exploring ways to provide a solution whilst retaining the benefits that a RDBMS provides. It may not be 'the answer' but I certainly feel it warrants some interest. Thank you, Emrul On Wed, Jun 29, 2011 at 9:37 AM, Radosław Smogura rsmog...@softperience.euwrote: On Tue, 28 Jun 2011 17:04:54 -0600, Rob Sargent wrote: On 06/28/2011 04:52 PM, Greg Smith wrote: On 06/28/2011 05:45 PM, Rob Sargent wrote: I think Greg might be forgetting that some of us don't always get to choose what we work on. I was in a shop that decided to go with multi-tenancy for reason both technical and um, er envious. There are certainly successful deployments of multi-tenant PostgreSQL out there, ones that make sense. What I was trying to communicate is that the particular variation proposed by this academic paper doesn't seem the right direction for PostgreSQL development to head in to me. This project is stubborn about resolving the problems people actually have, and the ones the paper tries to solve are not the ones I've seen in my own experiments in multi-tenant deployments. Yes, your point is well taken here, and that wasn't even hinted at in my previous (top! oops) post. My point was that hacks in the field (i.e. me) will have to do multi-tenancy on postgres and though this implementation may not become the answer, any leg up would be appreciated. I think this may be quite interesting solution. Actually I created such approach, for many reasons, but it's hard-coded, I mean in any place when query is executed I add this tenancy id, I called it differently, and it works perfectly. But such feature will not grow quite fast until PostgreSQL ecosystem will not grow, for example I see problems with Java + Hibernate + Caching, when tenancy id will be hidden, actually You may query for two different objects with same id, if we will allow dynamic tanacy switch (should be done, as You will loose connection pool benefits). Regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Real type with zero
Your question may suggest you are more interested in storing value, as decimal not real, it's more secure to use this way for moneys, but even with decimal your trailing zeros may be removed. Regards, Radoslaw Smogura -Original Message- From: Condor Sent: 29 czerwca 2011 13:24 To: pgsql-general@postgresql.org Subject: [GENERAL] Real type with zero Hello, how I can tell my postgresql to store last zero of real type ? I put value 2.30 and when I select that column i see 2.3 without zero. -- Regards, Condor -- 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] Real type with zero
On 29/06/2011 7:24 PM, Condor wrote: Hello, how I can tell my postgresql to store last zero of real type ? I put value 2.30 and when I select that column i see 2.3 without zero. The real data type is an IEEE 754 floating point number. See: http://en.wikipedia.org/wiki/Floating_point http://steve.hollasch.net/cgindex/coding/ieeefloat.html It doesn't store any information about formatting or layout. If you want to retain that information, you'll need to use NUMERIC or just store your numbers as formatted strings. Note that NUMERIC doesn't store error ranges and its formatting isn't preserved by most arithmetic operations; it's not a full scientific error-bounded numeric type. regress= SELECT '4401.00100'::numeric; numeric 4401.00100 (1 row) regress= SELECT '4401.00100'::float; float8 -- 4401.001 (1 row) -- Craig Ringer -- 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] point types in DISTINCT queries
On Wed, Jun 29, 2011 at 06:53, Jeff Davis pg...@j-davis.com wrote: On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php However I wanted to see if it was still necessary that I would need the complete btree operator class to run such a query. Yes, the default btree operator class is used to find the equality operator. Even though you have defined the operator =, postgresql doesn't rely on that meaning equals -- the btree operator class is what imparts that meaning. Are there plans to have a defined = operator on the point type? I can understand how the other geometric types, = would represent area, but AFAIK I think = could be safely applied on a point type (and i realize I could submit a patch for that :-) maybe depending on the resolution to this / refreshing my C...). The built-in geometric types haven't received a lot of attention lately. Most people who use geometric data use the PostGIS extension, which is a sophisticated extension that can deal with that kind of data. You might want to check that out and see if it meets your needs. Perhaps someone is interested in bringing the built-in geometric types up to speed; but I think most of the interest is moving things like this out to extensions where they can be more easily be maintained by interested parties. Given that they are the only ones supporting knn-gist, I would expect them to actually become *more* popular with 9.1 - at least until such time as postgis adds support for it... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] PLPGSQL SETOF functions
Please reply to the list in the future. I don't believe you can do that. Sim On 06/29/2011 04:39 PM, David Greco wrote: Thanks that works pretty well. Is it possible to fetch the all the return of dates_pkg.getbusinessdays() into a single variable at once? i.e. in Oracle I would do something like CRATE table_type as TABLE of TYPE record_type; declare allrows table_type; BEGIN allrows := dates_pkg.getbusinessdays(); END; And allrows would be a collection that I can iterate over at my leisure. I have to problem writing future code to just do a for loop over the select, but while migrating existing code I'd rather keep it as intact as possible. 1) If you declare a return type setof TABLENAME the resultset willcontain rows with field definitions like the table. 2) To call the function from another plpgsql function use: declare row record begin for row in select * from dates_pkg.getbusinessdays(...) Loop ...process... end loop ... end seehttp://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING On 06/28/2011 09:34 PM, David Greco wrote: I am porting some Oracle code to PLPGSQL and am having a problem withfunctions that return SETOF datatype. In Oracle, the functions I'mporting return a TABLE of TYPE datatype, this TABLE being itself anamed type. I am not aware of how to do this in PLPGSQL. Consider a function with header: CREATE OR REPLACE FUNCTIONdates_pkg.getbusinessdays(pstartdate timestamp with time zone,penddate timestamp with time zone) RETURNS SETOF timestamp with timezone AS I can easily call this function in SQL like so: select * from dates_pkg.getbusinessdays( now(), now()+ INTERVAL '7' day ) as business_day; However, I can't figure out how to call this function from anotherplpgsql function. Any hints? ~Dave Greco
Re: [GENERAL] point types in DISTINCT queries
On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz jonathan.k...@excoventures.com wrote: On Jun 29, 2011, at 10:25 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, Jun 29, 2011 at 06:53, Jeff Davis pg...@j-davis.com wrote: On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php However I wanted to see if it was still necessary that I would need the complete btree operator class to run such a query. Yes, the default btree operator class is used to find the equality operator. Even though you have defined the operator =, postgresql doesn't rely on that meaning equals -- the btree operator class is what imparts that meaning. Are there plans to have a defined = operator on the point type? I can understand how the other geometric types, = would represent area, but AFAIK I think = could be safely applied on a point type (and i realize I could submit a patch for that :-) maybe depending on the resolution to this / refreshing my C...). The built-in geometric types haven't received a lot of attention lately. Most people who use geometric data use the PostGIS extension, which is a sophisticated extension that can deal with that kind of data. You might want to check that out and see if it meets your needs. Perhaps someone is interested in bringing the built-in geometric types up to speed; but I think most of the interest is moving things like this out to extensions where they can be more easily be maintained by interested parties. Given that they are the only ones supporting knn-gist, I would expect them to actually become *more* popular with 9.1 - at least until such time as postgis adds support for it... In fact that is my use-case - I will be performing nearest-neighbor lookups (and will be running 9.1b2 on this data set shortly). However, because most of the geospatial work is relatively straightforward, I didn't want to use PostGIS for this application. But that might change in the near future depending on the requirements. But for now tasks like ensuing uniqueness amongst points are slightly more difficult. My current solution is breaking out the (x,y) coords into different columns Have you tried using an exclusion constraint? Not entirely sure, but I think that might work. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] point types in DISTINCT queries
On Jun 29, 2011, at 10:25 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, Jun 29, 2011 at 06:53, Jeff Davis pg...@j-davis.com wrote: On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php However I wanted to see if it was still necessary that I would need the complete btree operator class to run such a query. Yes, the default btree operator class is used to find the equality operator. Even though you have defined the operator =, postgresql doesn't rely on that meaning equals -- the btree operator class is what imparts that meaning. Are there plans to have a defined = operator on the point type? I can understand how the other geometric types, = would represent area, but AFAIK I think = could be safely applied on a point type (and i realize I could submit a patch for that :-) maybe depending on the resolution to this / refreshing my C...). The built-in geometric types haven't received a lot of attention lately. Most people who use geometric data use the PostGIS extension, which is a sophisticated extension that can deal with that kind of data. You might want to check that out and see if it meets your needs. Perhaps someone is interested in bringing the built-in geometric types up to speed; but I think most of the interest is moving things like this out to extensions where they can be more easily be maintained by interested parties. Given that they are the only ones supporting knn-gist, I would expect them to actually become *more* popular with 9.1 - at least until such time as postgis adds support for it... In fact that is my use-case - I will be performing nearest-neighbor lookups (and will be running 9.1b2 on this data set shortly). However, because most of the geospatial work is relatively straightforward, I didn't want to use PostGIS for this application. But that might change in the near future depending on the requirements. But for now tasks like ensuing uniqueness amongst points are slightly more difficult. My current solution is breaking out the (x,y) coords into different columns Jonathan -- 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] point types in DISTINCT queries
On Jun 29, 2011, at 10:42 AM, Magnus Hagander wrote: On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz jonathan.k...@excoventures.com wrote: In fact that is my use-case - I will be performing nearest-neighbor lookups (and will be running 9.1b2 on this data set shortly). However, because most of the geospatial work is relatively straightforward, I didn't want to use PostGIS for this application. But that might change in the near future depending on the requirements. But for now tasks like ensuing uniqueness amongst points are slightly more difficult. My current solution is breaking out the (x,y) coords into different columns Have you tried using an exclusion constraint? Not entirely sure, but I think that might work. Did a quick experiment: Using =~ ALTER TABLE a ADD EXCLUDE USING gist (geocode WITH ~=); Results: ERROR: could not create exclusion constraint a_geocode_excl DETAIL: Key (geocode)=((33.8367126,-117.9164627)) conflicts with key (geocode)=((33.8367128,-117.9164627)). Which means it *should* work, but first I would need to clean up the data and find the duplicates. I was hoping this might work: SELECT geocode, count(*) FROM a GROUP BY a.geocode HAVING count(*) 1; But: ERROR: could not identify an equality operator for type point So I would have to just find the points one-by-one until the exclusion constraint passes. Now, using the custom = operator: ALTER TABLE app_address ADD EXCLUDE USING gist (geocode WITH =); Results: ERROR: operator =(point,point) is not a member of operator family point_ops DETAIL: The exclusion operator must be related to the index operator class for the constraint. Jonathan -- 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] Real type with zero
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz Sent: Wednesday, June 29, 2011 7:44 AM To: con...@stz-bg.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Real type with zero what you probably looking for is formatting the output into a string. Postgresql will store it as 2.3, because that is what 2.30 is anyway. Its up to you to format it before passing it on to the user/business logic/whatever. I presume (and even if not) the OP is looking to keep the known precision of the value. If I look at 2.3 I do not know whether I have precision of measurement only to the tenths or whether I had higher precision but all positions beyond the tenths are zero. Aside from storing the true precision in a separate integer field what solution is there is this situation. I guess defining numeric(S,P) works although I haven't done much actual work with precision in the database and so I do not know whether it is truly sufficient. I would guess not since there may be cases where the known precision is less than the defined precision and so the numeric(S,P) data type will over specify the precision in those cases. This is beginning to sound like a varchar(n) versus text argument... 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] point types in DISTINCT queries
On Wed, 2011-06-29 at 11:37 -0400, Jonathan S. Katz wrote: Which means it *should* work, but first I would need to clean up the data and find the duplicates. I was hoping this might work: SELECT geocode, count(*) FROM a GROUP BY a.geocode HAVING count(*) 1; Maybe you could use a self-join as a workaround for now, just to clean up the data? SELECT geocode, other_columns from a a1, a a2 where a1.other_columns a2.other_columns and a1.geocode ~= a2.geocode; 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/
Re: [GENERAL] Real type with zero
On Jun 29, 2011, at 9:50 AM, David Johnston wrote: Aside from storing the true precision in a separate integer field what solution is there is this situation. I think the only other way would be a custom data type encapsulating those 2 bits of info. Which might be the best solution, since if you really need to maintain info about the significant digits of measurements, you need that to carry through properly in calculations with those numbers. OTOH, it's possible to have that info in a separate field, and leave with clients the responsibility for correct calculations... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] point types in DISTINCT queries
On Wed, 2011-06-29 at 11:37 -0400, Jonathan S. Katz wrote: Which means it *should* work, but first I would need to clean up the data and find the duplicates. I was hoping this might work: SELECT geocode, count(*) FROM a GROUP BY a.geocode HAVING count(*) 1; Maybe you could use a self-join as a workaround for now, just to clean up the data? SELECT geocode, other_columns from a a1, a a2 where a1.other_columns a2.other_columns and a1.geocode ~= a2.geocode; That worked perfectly - turned out it was just two rows. And subsequently executing the exclusion constraint on =~ also worked perfectly as expected. The larger issue I face with now is slightly out of my control without further hacking. I'm developing an app with Django and I wrote an extension that allows me to use the point type natively in Python. I ran into the original issue while an automatically generated query was executed in the admin section. I know this could be viewed as something pertaining to Django, but the goal I had in mind was making PostgreSQL functionality more accessible in a different software layer. I will find a workaround for the above, as I am sure I can do some application-level hacking. Thanks for your help! Jonathan -- 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ -- 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Grace the ossp-uuid libraries have no make system to create a 64 bit version and I guess there are some technical reasons with the compiler. You can run them on Linux 64 bit and Windows 32 bit only. This is the reason I'd like to see native UUID support built in to the datatype. Best Regards Michael Gould Grace Batumbya grace.batum...@senecac.on.ca wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- Grace Batumbya Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 a href=http://cdot.senecac.on.ca/; target=_blankcdot.senecac.on.ca/a -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Thanks... I didn't know that this had been done. Will be downloading shortly, thanks Mike Gould Hiroshi Saito hiro...@winpg.jp wrote: Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ -- 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Thanks Hiroshi, that solved the problem. If you do not mind, how did you go about to build ossp-uuid? *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ On 6/29/2011 12:38 PM, Hiroshi Saito wrote: Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/
Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Hi. here is an excuse... http://archives.postgresql.org/pgsql-general/2011-06/msg00738.php Regard, Hiroshi Saito (2011/06/30 1:50), Grace Batumbya wrote: Thanks Hiroshi, that solved the problem. If you do not mind, how did you go about to build ossp-uuid? *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ On 6/29/2011 12:38 PM, Hiroshi Saito wrote: Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Long Query and User Session
Hi! I want to ask that what happens if a long query running and user session timeout reached? 1.) For example: somebody starts a very long query or statistical stored procedure. The session timeout is 5 minutes, and the session exhausted this time. What happens? a.) The Query/STP automatically aborted by PGSQL, the resources released. b.) The Query/STP continue working b1) For unlimited time (while it reach the end of Qry/STP) b2) For limited time by a system parameter (max_query_running_time) but user is locked out from session. c.) The Query/STP working time is make the session alive, it is defined as user interaction too, so the session ended on Qry/STP end + 5 minutes. 2.) Another question based on this that can I force PGSQL server to abort and release a session that got infinite loop (as admin, to close any session?)? 3.) What happens if the client is disconnecting (the program died, or network connection died) while Query/STP running? 4.) And: is PGSQL supports aborting of the long Query basically? Thanks for your help: dd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How did I get 8 Exclusive locks on the same table? And how many locks is too many?
Hi, We use the fine Bucardo check_postgres Nagios plugin, and it reported a CRITICAL level spike in database locks (171 locks). I looked closely at my database logs and found an even bigger spike just a few minutes earlier (208 locks). I saw 8 EXCLUSIVE locks on table X. All of these queries completed within 5 seconds. (I know that because they do not appear in my slow query log.) The fine Postgres manual states: EXCLUSIVE lock: Only reads from the table can proceed in parallel with a transaction holding this lock mode. This lock mode is not automatically acquired on user tables by any PostgreSQL command. My questions: 1. How I could have had eight queries all with EXCLUSIVE locks on the same table? Wouldn't have Postgres only allowed one EXCLUSIVE lock at a time? 2. Since there was no sign of any trouble in the application itself (everything appeared to work fine), I'm wondering whether we need to increase our threshold for critical for number of locks, and what factors are involved in deciding what to set it to (double it? 10x it?) What value to use... We recently upgraded our server and it is pretty beefy compared to the workload being thrown at it. (For example, we can do 800 Postgres transactions per second without breaking a sweat on the disk subsystem, which records about 150,000 write IOPS, showing 3% utilization on the disk subsystem.) Best, -at -- 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] How did I get 8 Exclusive locks on the same table? And how many locks is too many?
In response to Aleksey Tsalolikhin atsaloli.t...@gmail.com: We use the fine Bucardo check_postgres Nagios plugin, and it reported a CRITICAL level spike in database locks (171 locks). I looked closely at my database logs and found an even bigger spike just a few minutes earlier (208 locks). I saw 8 EXCLUSIVE locks on table X. All of these queries completed within 5 seconds. (I know that because they do not appear in my slow query log.) What manner did you use to determine this? It's not possible to have multiple table-level EXCLUSIVE locks on a single table, since a single table level EXCLUSIVE lock will cause other lock attempts to block. However, it's possible to have multiple row-level EXCLUSIVE locks, 1 per row. Did the mysterious magic that you used to determine that there were 8 EXCLUSIVE locks tell you whether they were table level or row level? If they're table level, then something is wrong either with PostgreSQL or your magic. If they're row level, then that's hardly unusual. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] FOREIGN TABLE with dblink
Hi Shigeru,
thank you for responding. This is going to be a great feature!
Regards,
Jasmin
2011/6/29 Shigeru Hanada shigeru.han...@gmail.com
Hi Jasmin,
(2011/06/16 19:40), Jasmin Dizdarevic wrote:
Hi,
is there any way to use the new foreign table feature with dblink?
That's almost clear to me:
CREATE FOREIGN DATA WRAPPER pgsql90;
CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr
'127.0.0.1', dbname 'lotty');
CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (user 'pgsql',
password '');
I would like to replace this
SELECT dblink_connect('myconn', 'srvlocal90');
SELECT * FROM dblink('myconn', 'select * from mytests.fttest') AS t(id
int,
myname text);
to something like this:
CREATE FOREIGN TABLE mytests.lnkto90_fttest (
id int,
myname text
)
SERVER srvlocal90 OPTIONS ()
SELECT * FROM mytests.lnkto90_fttest;
Your assumption is exactly what the foreign table feature aims at;
foreign table feature would allow you to access external data via a SQL
statement which conformed with the SQL statement, though foreign tables
are read-only in the first release.
But unfortunately FDW for PostgreSQL won't be shipped with 9.1 release.
Hopefully, PostgreSQL-FDW for 9.1 might be released as an external
module, and then you would be able to replace dblink with foreign tables.
Regards,
--
Shigeru Hanada
[GENERAL] Inheritence issue scheme advice?
I'm obviously new. But making great progress in PostgreSQL with my new application... Setup: I'm running on MAC. Postgre 9.0.4 Virtual Machine with application dev in Linux. Problem: I like many other have come across the inherit issues. I found the thread here about such issue... http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html I grabbed the fk_inheritance.v1.patch file and have been trying to install it for the last two hours. -- Got some help in hackers space so figured this out. BUT was it was recommended not to utilize a hacked version of PostgreSQL. Also person said that partitioning might be a way to go... BUT Partitioning becomes impossible as I'd have to hunt down every single row from every table within the hierarchy when needed. I've got an object driven system with permissions for users so I'll easily have thousands of rows to manage across 100's of tables. For inheritance I'm using it for the following. ONLY on/with UNIQUE CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding that shouldn't be an issues as there shouldn't any duplicate entries that cause a deadlock? -- So I would think this patch would be ok? Is there another way that won't be such a headache - cost tons of man hours - and still be efficient? I'm open for anything as I haven't been able to make any progress today :(. - Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4536626.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Inheritence issue scheme advice?
On 2011-06-29 22:54, Casey Havenor wrote: Problem: I like many other have come across the inherit issues. I found the thread here about such issue... http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html I grabbed the fk_inheritance.v1.patch file and have been trying to install it for the last two hours. -- Got some help in hackers space so figured this out. BUT was it was recommended not to utilize a hacked version of PostgreSQL. For inheritance I'm using it for the following. ONLY on/with UNIQUE CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding that shouldn't be an issues as there shouldn't any duplicate entries that cause a deadlock? -- So I would think this patch would be ok? There are currently two caveats with the patch you mention 1: the user has to ensure global uniqueness of all pk's in an inheritance hierarchy. This is not a problem at all if e.g. your root relation has a pk with e.g. a default value from a sequence. The childs will inherit that default value from the same sequence. 2: the patch enables inserting rows in a relation that has a fk to a inheritance parent/root, with an fk key value that is found in one of the child relations of the refered relation. The patch as is fails to block deleting the referred record in the child relation: that check currently only works for referred records in actual relation the fk points to, not it's childs. It is not impossible to add this, but it just hasn't been programmed yet. So a trigger check to prevent these deletions also has to be made in user space. I'd very much appreciate any feedback you have on the patch, if it matches your usecase. regards, -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data -- 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] Real type with zero
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Johnston Sent: Wednesday, June 29, 2011 11:51 AM To: 'Grzegorz Jaśkiewicz'; con...@stz-bg.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Real type with zero Aside from storing the true precision in a separate integer field what solution is there is this situation. I guess defining numeric(S,P) works although I haven't done much actual work with precision in the database and so I do not know whether it is truly sufficient. I would guess not since there may be cases where the known precision is less than the defined precision and so the numeric(S,P) data type will over specify the precision in those cases. This is beginning to sound like a varchar(n) versus text argument... David J. .. Now that I've re-read the section on numeric I have a couple of further points. 1) I indeed reversed scale and precision; but context should make that obvious. 2) You can specify a known precision (and default scale of 0) but cannot specify a specific known scale with unbounded precision. From the documentation I presume you can specify numeric(1000-s, s); where 's' is the desired scale and, at current, capture all possible values that have exactly 2 positions of scale. The only, practically meaningless, downside is if the upper-limit of precision ever were to change you would need to redefine all of these data types with the new precision to keep the same semantics. Question: I store and retrieve (with some manipulation) currency (dollar) values often and use numeric to store them. I generally pick a reasonable precision, around 10 or so, and use 2 for the scale. Would specifying numeric(9998,2) result in identical performance and storage - for a given value - compared to storing that value in a numeric(10,2)? Also, is there any reason why numeric(0,n) couldn't be used as a synonym for numeric(MAX-n, n)? Zero precision with a non-zero scale is meaningless so overloading doesn't seem to be that big an issue and this way you are not requiring the user to know the details of the implementation in order to pick the proper value for MAX. Obviously polymorphism rules will not allow for numeric(scale) to be a valid construct since numeric(precision) would cause an ambiguity. 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] LOCK TABLE permission requirements
On Wed, Jun 29, 2011 at 7:48 AM, Florian Weimer fwei...@bfk.de wrote: I've been looking around in the 9.0 documentation, but couldn't find the permission requirements for LOCK TABLE (in particular, LOCK TABLE IN SHARE MODE). From the source, you need at least one of UPDATE, DELETE or TRUNCATE. Is there a reason why the INSERT privilege is not sufficient for LOCK TABLE, or is this just an oversight? The comments on this thread outline some reasons the permissions for LOCK TABLE are setup the way they are: http://archives.postgresql.org/pgsql-hackers/2010-11/msg01819.php Basically, if you have UPDATE, DELETE, or TRUNCATE privileges you can potentially lock out competing sessions on a table, similar to what some forms of LOCK TABLE would do; just having INSERT privileges doesn't necessarily give you that power. Josh -- 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
On 30/06/2011 12:42 AM, Michael Gould wrote: Grace the ossp-uuid libraries have no make system to create a 64 bit version and I guess there are some technical reasons with the compiler. You can run them on Linux 64 bit and Windows 32 bit only. This is the reason I'd like to see native UUID support built in to the datatype. Anyone interested in this might want to start here: http://msdn.microsoft.com/en-us/library/aa379205(v=vs.85).aspx http://msdn.microsoft.com/en-us/library/aa379205%28v=vs.85%29.aspx http://msdn.microsoft.com/en-us/library/aa379322(v=vs.85).aspx http://msdn.microsoft.com/en-us/library/aa379322%28v=vs.85%29.aspx http://msdn.microsoft.com/en-us/library/aa379358(v=vs.85).aspx http://msdn.microsoft.com/en-us/library/aa379358%28v=vs.85%29.aspx I don't use UUIDs and have way too much on my plate alread so I'm not volunteering for this one. -- Craig Ringer
Re: [GENERAL] Inheritence issue scheme advice?
What has been your take on the patch - any long term drawbacks or any other functions / triggers that I'll have to stay away from when using this patch within PostgreSQL? Also any tutorials on how to apply the patch under windows/Linux/mac would be appreciated? - Warmest regards, Casey Havenor -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inheritence-issue-scheme-advice-tp4536626p4537668.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
