[GENERAL] Could not read block of temporary files
Hi all, I'm creating a table from a select query. During the execution it errors with: ERROR: could not read block 13 of temporary file: Success I am running Postgresql 9.1 on a Debian/Linux server. Does anyone have any suggestions on what could be causing this? I checked diskspace and permissions for the tablespace directory and pgsql_tmp directory and they are fine. Many thanks R Clarke
Re: [GENERAL] Index usage with slow query
Hi Bill,
Thanks for the reply. Here's the EXPLAIN output of a couple of the queries:
SELECT the_geom,oid from mytable
where the_geom ST_GeomFromText('POLYGON((529342.334095833
180696.22173,529342.334095833 181533.44595,530964.336820833
181533.44595,530964.336820833 180696.22173,529342.334095833
180696.22173))',find_srid('','mytable','the_geom'))
and (floor = 'gf' AND source_id = '16701' AND class = 'General')
Bitmap Heap Scan on mytable (cost=1212.62..1580.71 rows=177 width=612)
Recheck Cond: ((source_id = 16701) AND (the_geom
'010320346C01000500'::geometry))
Filter: (((floor)::text = 'gf'::text) AND ((class)::text =
'General'::text))
- BitmapAnd (cost=1212.62..1212.62 rows=184 width=0)
- Bitmap Index Scan on idx_source_id (cost=0.00..433.25
rows=33149 width=0)
Index Cond: (source_id = 16701)
- Bitmap Index Scan on idx_the_geom_gist (cost=0.00..779.03
rows=38654 width=0)
Index Cond: (the_geom
'010320346C01000500'::geometry)
SELECT the_geom,oid from mytable
where the_geom ST_GeomFromText('POLYGON((415995.148624997
433101.445479165,415995.148624997 433326.320145835,416466.572625003
433326.320145835,416466.572625003 433101.445479165,415995.148624997
433101.445479165))',find_srid('','mytable','the_geom'))
and (floor = 'gf' AND source_id = '20427' AND class = 'General')
Bitmap Heap Scan on mytable (cost=21.41..23.44 rows=1 width=612)
Recheck Cond: ((the_geom
'010320346C01000500'::geometry) AND (source_id = 20427))
Filter: (((floor)::text = 'gf'::text) AND ((class)::text =
'General'::text))
- BitmapAnd (cost=21.41..21.41 rows=1 width=0)
- Bitmap Index Scan on idx_the_geom_gist (cost=0.00..4.18
rows=141 width=0)
Index Cond: (the_geom
'010320346C01000500'::geometry)
- Bitmap Index Scan on idx_source_id (cost=0.00..16.97
rows=1112 width=0)
Index Cond: (source_id = 20427)
There is no unique field in the table. And unfortunately the performance is
unacceptable. The logs show that the first query when it was execute took
70466.757 ms where as the second one took 11032.459 ms.
I've begun to create a duplicate environment to play with so hopefully I'm
able to weed out a solution.
On Wed, Jul 23, 2014 at 3:57 PM, Bill Moran wmo...@potentialtech.com
wrote:
On Wed, 23 Jul 2014 10:45:56 +0100
Rebecca Clarke r.clark...@gmail.com wrote:
Hi all,
Looking for some advice regarding a slow query I have and indexing.
I'm using postgresql 9.1 and this is my table that has around 680
rows:
CREATE TABLE mytable
(
class character varying,
floor character varying,
source_id integer,
the_geom geometry
)
WITH (
OIDS=TRUE
);
INDEX idx_source_id
USING btree
(source_id);
INDEX idx_the_geom_gist
USING gist
(the_geom);
This table is constantly hit with the below query (not always the same
values in the where). The only difference between queries are the values
in
the where clause:
SELECT the_geom,oid from mytable
WHERE
the_geom ST_GeomFromText('POLYGON((529342.334095833
180696.22173,529342.334095833 181533.44595,530964.336820833
181533.44595,530964.336820833 180696.22173,529342.334095833
180696.22173))',find_srid('','mytable','the_geom'))
AND
(floor = 'gf' AND source_id = '689' AND class = 'General')
As the table has increased in size, this query has become slower, so I
made
this index:
INDEX idx_floor_sourceid_class
USING btree
(floor, source_id, class);
When I run an EXPLAIN and EXPLAIN ANALYZE the query isn't using this new
index.
Sometimes it uses just idx_the_geom_gist
other times it uses idx_the_geom_gist and idx_source_id
I don't understand why it's inconsistent in its use of indexes when the
query is always the same structure, just different where clause values,
and
I don't understand why it's not using the new index either.
It depends on the data. The planner will make estimates on what the
fastest
way to execute will be based on a lot of things, one of which is how
helpful
an index is really expected to be. Since your indexes aren't arranged to
allow an index-only scan (although I don't remember if 9.1 had index-only
scans yet ...) it will have to use the index to narrow down the rows, then
load up the rows and filter them further (you didn't provide explain
output,
but I'll bet a dozen nickels that's what it says). So if the values in
source_id are unique enough that the planner doesn't think that
idx_floor_sourceid_class will narrow the results any better than
idx_source_id, it will use the former because it's a smaller index and will
require less disk fetches to load it.
Of course, without explain output, I'm assuming a lot. But the basic
operation
still stands, indexes aren't always guaranteed to be faster than
[GENERAL] Index usage with slow query
Hi all,
Looking for some advice regarding a slow query I have and indexing.
I'm using postgresql 9.1 and this is my table that has around 680 rows:
CREATE TABLE mytable
(
class character varying,
floor character varying,
source_id integer,
the_geom geometry
)
WITH (
OIDS=TRUE
);
INDEX idx_source_id
USING btree
(source_id);
INDEX idx_the_geom_gist
USING gist
(the_geom);
This table is constantly hit with the below query (not always the same
values in the where). The only difference between queries are the values in
the where clause:
SELECT the_geom,oid from mytable
WHERE
the_geom ST_GeomFromText('POLYGON((529342.334095833
180696.22173,529342.334095833 181533.44595,530964.336820833
181533.44595,530964.336820833 180696.22173,529342.334095833
180696.22173))',find_srid('','mytable','the_geom'))
AND
(floor = 'gf' AND source_id = '689' AND class = 'General')
As the table has increased in size, this query has become slower, so I made
this index:
INDEX idx_floor_sourceid_class
USING btree
(floor, source_id, class);
When I run an EXPLAIN and EXPLAIN ANALYZE the query isn't using this new
index.
Sometimes it uses just idx_the_geom_gist
other times it uses idx_the_geom_gist and idx_source_id
I don't understand why it's inconsistent in its use of indexes when the
query is always the same structure, just different where clause values, and
I don't understand why it's not using the new index either.
Would love some help with this. I'm not sure where I'm going wrong.
Thanks in advance.
[GENERAL] What query currently running within function
Hi all, Presently I'm executing a function that runs many queries within it. select * from _myfunction(); Is there a way to see what query it is up to within the function? When I do a select of pg_stat_activity it just shows me the _myfunction() query. I'm running postgresql 9.1 Thanks in advance.
[GENERAL] Track changes to function code
Hi all, At present when a function is being edited we keep note of when and by who within comments in the function's code. Is there, or can anyone recommend, any open source software that tracks function activity when it comes to edits (not executions)? I tried searching on the web, but all I find concerns the tracking of data changes. Thanks in advance.
Re: [GENERAL] Track changes to function code
Thanks guys. I'll check that out. On Mon, Jul 21, 2014 at 2:12 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi 2014-07-21 14:36 GMT+02:00 Jacob Bunk Nielsen ja...@bunk.cc: Rebecca Clarke r.clark...@gmail.com writes: At present when a function is being edited we keep note of when and by who within comments in the function's code. That sounds cumbersome. Is there, or can anyone recommend, any open source software that tracks function activity when it comes to edits (not executions)? How about keeping the code outside of the database in a VCS such as git, Subversion or whatever you are used to using for other code projects? That will also make it possible easily go back to previous versions if you should need to some day. You simply put your functions in one or more .sql files that you version control using e.g. git. Once you have written your functions you can put them in you database using: psql -f myfile.sql yes, it is good way stored procedures are code as any other and there are same rules. Use files, use a your preferred editor, use makefiles, use GIT Regards Pavel Stehule -- Jacob -- 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] Re: collecting employees who completed 5 and 10 years in the current month
Right you are David re my first query. That'll be more appropriate if you want to establish if they're in their 5th year, or 10th year. On Mon, Jun 30, 2014 at 6:08 PM, David G Johnston david.g.johns...@gmail.com wrote: Rebecca Clarke-2 wrote create view vw_employee as select * from employees where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') ) This does not give the correct answer to the poster's question - the LIKE with a trailing % will pick up non-round intervals. create view vw_employee as select * from employees where ((to_char(joining_date, '-MM') = to_char((now() - interval '5 years'), '-MM') ) or (to_char(joining_date, '-MM') = to_char((now() - interval '10 years'), '-MM'))) This works - find out what year-month it was x years ago and compare it to the corresponding year-month of the requested date. If one were to be doing this often it would probably be worth while to either use a functional index or a trigger-maintained field to store the to_char(joining_date) calculation. WHERE joining_date_yearmonth = ANY( ARRAY['2009-06','1999-06']::text[] ); Was also pondering using a VARIADIC function to pass in integer year(s), which would then be converted into the corresponding array. Haven't actually played with the above and so not sure how index-friendly the =ANY(...) construct is but it does allow you to avoid add entire OR clauses and instead simply supply a different comparison array. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/collecting-employees-who-completed-5-and-10-years-in-the-current-month-tp5809762p5809828.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] collecting employees who completed 5 and 10 years in the current month
From my understanding of what you're saying, you want all the employees that have a 5 year, or 10 year anniversary between today and the start of the current month? If that is the case, then this is what I came up with: select employee_name, to_char(current_date, '')::integer - to_char(joining_date::date, '')::integer as milestone, joining_date + (current_date - joining_date) as anniversary_date from employees where ((joining_date::date + interval '5 years') = to_char(current_date, '-MM-1')::date and (joining_date::date + interval '5 years') = current_date) or ((joining_date::date + interval '10 years') = to_char(current_date, '-MM-1')::date and (joining_date::date + interval '10 years') = current_date) Once again, excuse any syntax errors. On Mon, Jun 30, 2014 at 5:15 PM, Arup Rakshit arupraks...@rocketmail.com wrote: On Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote: Hi Arup, Two ways come to mind for me. They're pretty much the same as Szymon's, just minus the sample table creation. I would suggest creating a view instead, so you can just select from it whenever you please. create view vw_employee as select * from employees where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') ) or create view vw_employee as select * from employees where ((to_char(joining_date, '-MM') = to_char((now() - interval '5 years'), '-MM') ) or (to_char(joining_date, '-MM') = to_char((now() - interval '10 years'), '-MM'))) Can this query be set up like :- Consider the below scenarios : Ram completed 5 years on 12/04/2014 Shyam completed 5 years on 21/04/2014 Ayan completed 10 years on 12/04/2014 and so on... Now consider the current month is *march*. I have 12 employees. Out of which above only completed 5 and 10 years. Thus my output should come as Name milestoneswhen Ram 512/04/2014 Shyam 5 21/04/2014 Ayan10 12/04/2014 -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- 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] collecting employees who completed 5 and 10 years in the current month
Hi Arup,
Two ways come to mind for me. They're pretty much the same as Szymon's,
just minus the sample table creation. I would suggest creating a view
instead, so you can just select from it whenever you please.
create view vw_employee as
select * from employees
where ((age(joining_date::date) like '5 years%') or
(age(joining_date::date) like '10 years%') )
or
create view vw_employee as
select * from employees
where
((to_char(joining_date, '-MM') = to_char((now() - interval '5
years'), '-MM') )
or
(to_char(joining_date, '-MM') = to_char((now() - interval '10
years'), '-MM')))
And then to check the employees who have completed 5 or 10 years, you'll
just do:
select * from vw_employee
This is done off the top of my head so there will likely be syntax errors,
but I hope this can give you a general idea.
- Rebecca
On Mon, Jun 30, 2014 at 12:00 PM, Szymon Guz mabew...@gmail.com wrote:
On 30 June 2014 12:38, Arup Rakshit arupraks...@rocketmail.com wrote:
I have employee table. Where I have a column joining_date. Now I am
looking for a way to get all employee, who completed 5 years, 10 years
current month. How to do so ? I am not able to figure this out.
Regards,
Arup Rakshit
Hi,
take a look at this example:
I've created a sample table:
create table users(id serial, joining_date date);
and filled it with sample data:
insert into users(joining_date) select now() - (j::text || 'days'
)::interval from generate_series(1,1) j;
Then the query showing up all users who complete 5 and 10 years this month
can look like:
with u as (
select id, date_trunc('month', age(now()::date, joining_date)) age
from users
)
select *
from u
where u.age in ('5 years', '10 years');
- Szymon
[GENERAL] Initial queries of day slow
Hi all. I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, are taking a long time when they are executed first thing in the morning (after the database has been inactive for several hours). After the first execution, everything is back to normal. A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script which does a VACUUM ANALYZE on each table. These are my details: Debian GNU/Linux 6.0 Postgresql 9.1 Memory 4GB shared_buffers = 1024MB work_mem = 16MB maintenance_work_mem = 128MB effective_cache_size = 2048MB Would love peoples opinions on what the issue could be. Thanks
Re: [GENERAL] Initial queries of day slow
Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow morning. I just tried it now on a query that took 109035.116 ms this morning (Which returns one row). It has returned 675.496 ms. I will run on this same query at 5am tomorrow. Thank you. At present we run pg_dumps every three hours. We orginally found autovacuum too intrusive so switched to manual. We've had no problems with performance at all, only this. We're going to turn autovacuum back on to see if it makes any impact to this particular issue. On Mon, Apr 7, 2014 at 10:50 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Rebecca Clarke wrote: I'm a bit stumped. At present I'm finding that queries to my database, that normally execute promptly, are taking a long time when they are executed first thing in the morning (after the database has been inactive for several hours). After the first execution, everything is back to normal. A while back I turned autovacuum off and now instead I run a daily cron at 3am that executes a script which does a VACUUM ANALYZE on each table. It could be that during the day the necessary pages are cached in the buffer pool or the file system cache, but have dropped out of the cache during the night. Try EXPLAIN (ANALYZE, BUFFERS) SELECT ... first thing in the morning and during the day and compare the shared read and shared hit values. It may well be the nightly VACUUM ANALYZE that does that - is autovacuum not doing ist job for you? Is there anything else going on on the machine during the night, like backups or batch jobs? Yours, Laurenz Albe
Re: [GENERAL] Initial queries of day slow
Hi Jeff Unfortunately it's not just the one particular query, there's no pattern that I can see besides the time they're being executed. We did go from Autovac only to nightly vac. I'm going to implement autovac again, we've been operating without for a few months now. Will run both nightly manual and autovac to see how things go. On a side not, we're not doing a vacuumdb, but individual vacuum analyze statements on each table. Not sure if that makes any difference. On Mon, Apr 7, 2014 at 9:13 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Apr 7, 2014 at 3:58 AM, Rebecca Clarke r.clark...@gmail.comwrote: Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow morning. I just tried it now on a query that took 109035.116 ms this morning (Which returns one row). It has returned 675.496 ms. I will run on this same query at 5am tomorrow. Thank you. If the problem is largely encapsulated by that one query, I'd just write a cron job to execute that query every morning 15 minutes before you open for business. At present we run pg_dumps every three hours. We orginally found autovacuum too intrusive so switched to manual. We've had no problems with performance at all, only this. We're going to turn autovacuum back on to see if it makes any impact to this particular issue. Did you go from 'Autovacuum only' to 'nightly vacuum, no autovac' in one step? Mostly likely adding the nightly vacuum while leaving autovac on would have solved the problem, while being less likely to cause other problems. (This is a side note--having autovac off is unlikely to be causing the particular problem you are reporting here.) Cheers, Jeff
Re: [GENERAL] .pgpass being ignored
I could be wrong, but shouldn't the owner of .pgpass be postgres? On Mon, Jun 24, 2013 at 3:17 PM, Ziggy Skalski zskal...@afilias.infowrote: On 13-06-21 06:19 PM, Stephen Rasku wrote: I am trying to write a script that will create and populate a database. I don't want to enter a password every time so I want to use a .pgpass file. It has the correct permissions: $ ls -l $PGPASSFILE -rw--- 1 Stephen staff 43 21 Jun 14:48 /Users/Stephen/.pgpass However, when I call createdb, it fails: $ createdb -h 192.168.1.4 -U postgres --no-password JobSearch createdb: could not connect to database postgres: fe_sendauth: no password supplied This is the contents of my .pgpass file: 192.168.1.4:5432:DatabaseName:**postgres:**thisIsTheCorrectPassword If I omit the --no-password option it will prompt me for a password and the command will succeed. I am using 9.0.10 from MacPorts. What am I doing wrong? ...Stephen Hi, Just going from a personal experience, have you tried to open the .pgpass file in vi and made sure there's no trailing spaces in your pgpass entry? That bit me once before :) Ziggy -- 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] Get data type aliases
That's great, thank you! worked like a charm. On Wed, Jun 12, 2013 at 8:31 PM, Alvaro Herrera alvhe...@2ndquadrant.comwrote: Rebecca Clarke escribió: Hi all. I have a function that has an attribute with datatype of character varying. In the pg_type table the oid of the data type points to type varchar Is there somewhere that identifies the alias for each type? Cast the type name to regtype. That outputs the official name. alvherre=# select 'int'::regtype; regtype - integer (1 fila) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[GENERAL] Get data type aliases
Hi all. I have a function that has an attribute with datatype of character varying. In the pg_type table the oid of the data type points to type varchar Is there somewhere that identifies the alias for each type? E.g. int4 = integer varchar = character varying. and so on. I can do an if statement in my code but I'd prefer not to. Thanks in advance. Rebecca
[GENERAL] Function tracking
Hi all I'm looking for suggestions on the best way to track the updates to a function. We have two databases, Dev Live, so I want to update Live with just the functions that have been modified in the DEV databas3e. Is there another, easier way to track the updates than manually recording it in a document? (I'm trying to eliminate human interference). Many thanks in advance Rebecca
[GENERAL] Storing Special Characters
Hi there.
This may be the wrong forum to inquire in, but I'd be grateful if I could
directed in the right direction if that is the case.
I am currently using Postgresql 9.1.
I have a table in which I want to store shop names. Some of the shop names
contain 'é' and '£'.
The query below works when I run it through pg_admin:
insert into retail(storename) values ('£'::character varying)
However, when I run the same query through pg_query in PHP, I get:
PHP Warning: pg_query(): Query failed: ERROR: invalid byte sequence for
encoding UTF8: 0xa3
I'd really like to find a solution to getting this code to work through
PHP. My PHP code is correct as it executes other queries (which have
no special characters) without complaint. Do I need to make any alterations
in Postgresql? Is my sql wrong? What is the difference in how its executed
though PgAdmin and PHP?
I've been relentlessly searching google for a solution to no avail.
Many thanks in advance.
R Clarke
[GENERAL] Databas has no Object Identifier Types Functions
Hi there, I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, e.g. regclass, regclassout. What's the best way to get these in to this database? I don't want to upgrade postgresql. There are other databases on the server that do have them. Thanks R Clarke
Re: [GENERAL] Databas has no Object Identifier Types Functions
Returns 0 rows. On Tue, Sep 4, 2012 at 11:41 AM, Pavan Deolasee pavan.deola...@gmail.comwrote: On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke r.clark...@gmail.comwrote: Hi there, I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, e.g. regclass, regclassout. Are you sure you don't have them ? I thought regclass is quite old, but I may be wrong. What does the following query returns ? SELECT * FROM pg_type WHERE typname = 'regclass'; Thanks, Pavan
[GENERAL] No Default Text Search Parser
Hi there Postgresql 8.4. I keep getting this error multiple times when I tried to access the pg_catalog in pg_admin: Cache lookup failed for text search parser 3722. The logs show that the query that was being executed was: (example is one of many) SELECT (SELECT t.alias FROM pg_catalog.ts_token_type(cfgparser) AS t WHERE t.tokid = maptokentype) AS tokenalias, dictname FROM pg_ts_config_map LEFT OUTER JOIN pg_ts_config ON mapcfg=pg_ts_config.oid LEFT OUTER JOIN pg_ts_dict ON mapdict=pg_ts_dict.oid WHERE mapcfg=11299::oid ORDER BY 1, mapseqno What I found was that my database does not have the default text search parser with oid 3722. Is someone able to tell me it's origin so I can back track and get it in the database. I could easily generate it manually but it will have a different oid which is a no go because pg_ts_parser specifies 3722 and the records in pg_ts_config specify parsar as 3722 also. Many thanks R Clarke
Re: [GENERAL] .pgpass not working
Hi Ben I had saved the .pgpass file in my home directory /home/user/.pgpass which works when I'm logged in as user. However, in order for me to use Slony, I had to be logged in as postgres user. I installed strace and ran my pg_dump test and found that it actually looks for the .pgpass file in /var/lib/postgresql (which I'm assuming is the postgres users home directory as this is the directory where I begin in when I log in). I made a copy of the .pgpass and saved it in that location and it worked! Many thanks. Rebecca On Fri, May 4, 2012 at 6:35 PM, Ben Chobot be...@silentmedia.com wrote: On May 4, 2012, at 9:30 AM, Rebecca Clarke wrote: I do not want to touch the pg_hba.conf so I have generated the .pgpass file. The permissions is set to 600, and I have correctly inputted the details into .pgpass, there are no leading spaces. *myhostname:myport:*:postgres:mypassword* However I am still prompted for a password. I have tested pg_dump as well and it prompts also. Does anyone have any suggestions on what may be the culprit. Is there somewhere I need to specify to tell the system to look into the .pgpass file? Where is the .pgpass file? If it's not in ~/.pgpass or doesn't have the right ownership (your permissions are good) then it won't be used. If it's in a different location, you might need to make use of the PGPASSFILE environment variable. If you really get stuck, you can always strace psql or pg_dump and see if it has problems opening your .pgpass file.
[GENERAL] .pgpass not working
Hi Apologies in advance if this is the wrong place to ask. I have Postres 8.4 and I am setting up replication with Slony. I'm having password issues with slony. I have roamed the net for a solution and the way to fix it is to use the .pgpass file or change pg_hba.conf to accept trusted connections. I do not want to touch the pg_hba.conf so I have generated the .pgpass file. The permissions is set to 600, and I have correctly inputted the details into .pgpass, there are no leading spaces. *myhostname:myport:*:postgres:mypassword* However I am still prompted for a password. I have tested pg_dump as well and it prompts also. Does anyone have any suggestions on what may be the culprit. Is there somewhere I need to specify to tell the system to look into the .pgpass file? many thanks rebecca
Re: [GENERAL] how to drop function?
DROP FUNCTION process_table; should work. On Tue, Nov 15, 2011 at 11:38 PM, J.V. jvsr...@gmail.com wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. -- 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] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist
Your right. I'm actually transferring from 8.2. Dumb moment for me there! I am using tsvector so unable to uninstall. I will look into documentation.. Thanks for your help. Rebecca On Thu, Oct 13, 2011 at 3:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rebecca Clarke rebe...@clarke.net.nz writes: I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the db schema on 8.3. When I pg_restore it to 8.4 I get the following: pg_restore: creating FUNCTION gtsq_in(cstring) pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 FUNCTION gtsq_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function gtsq_in in file /usr/lib/postgresql/8.4/lib/tsearch2.so Command was: CREATE FUNCTION gtsq_in(cstring) RETURNS gtsq AS '$libdir/tsearch2', 'gtsq_in' LANGUAGE c STRICT; Were you *really* on 8.3 before? Because that function hasn't existed since 8.2. You need to follow the procedure for updating pre-8.3 tsearch2 installations, as described in the manual: http://www.postgresql.org/docs/8.4/static/textsearch-migration.html (I believe BTW that some errors of the sort you show above are expected while doing this process. If that's what you're doing, just ignore it.) Alternatively, if you weren't actually doing anything with the contrib/tsearch2 functions, you could just uninstall them from the older database and then repeat the dump. regards, tom lane
[GENERAL] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist
Hi I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the db schema on 8.3. When I pg_restore it to 8.4 I get the following: pg_restore: creating FUNCTION gtsq_in(cstring) pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 FUNCTION gtsq_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function gtsq_in in file /usr/lib/postgresql/8.4/lib/tsearch2.so Command was: CREATE FUNCTION gtsq_in(cstring) RETURNS gtsq AS '$libdir/tsearch2', 'gtsq_in' LANGUAGE c STRICT; I'm getting the same error when it tries to create function gtsq_out(cstring), gtsvector_out(cstring) and gtsvector_in(cstring) as well I've run the tsearch2.sql in /usr/share/postgresql/8.4/contrib but still no go. I can't seem to find any info on web either. Rebecca
[GENERAL] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist
Hi I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the db schema on 8.3. When I pg_restore it to 8.4 I get the following: pg_restore: creating FUNCTION gtsq_in(cstring) pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 FUNCTION gtsq_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function gtsq_in in file /usr/lib/postgresql/8.4/lib/tsearch2.so Command was: CREATE FUNCTION gtsq_in(cstring) RETURNS gtsq AS '$libdir/tsearch2', 'gtsq_in' LANGUAGE c STRICT; I'm getting the same error when it tries to create function gtsq_out(cstring), gtsvector_out(cstring) and gtsvector_in(cstring) as well I've run the tsearch2.sql in /usr/share/postgresql/8.4/contrib but still no go. I can't seem to find any info on web either. Rebecca
Re: [GENERAL] Connection Error during Pg_restore
That worked thank you On Wed, Aug 17, 2011 at 1:01 PM, raghu ram raghuchenn...@gmail.com wrote: On Wed, Aug 17, 2011 at 4:32 PM, Rebecca Clarke rebe...@clarke.net.nzwrote: Hi there I'm doing a restore of a large table. The backup file is 18gb. When I run the restore after sometime it comes up with this error while it is restoring the data. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3022; 0 4287406059 TABLE DATA tbl_exampletable postgres pg_restore: [archiver (db)] COPY failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. WARNING: errors ignored on restore: 1 It will work if I use COPY but the table is so big I need to use pg_dump to compress it. Try below steps to restore data for larger table: 1. Create a table structure 2. copy data into .csv file copy item_trans to '/opt/item_trans.csv' WITH csv; 3. restore data with below command nohup psql -d postgres -p 5432 -U postgres -c copy item_trans from '/opt/item_trans.csv' WITH csv; /tmp/restore.log 2/tmp/restore.log --Raghu Ram
[GENERAL] Connection Error during Pg_restore
Hi there I'm doing a restore of a large table. The backup file is 18gb. When I run the restore after sometime it comes up with this error while it is restoring the data. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3022; 0 4287406059 TABLE DATA tbl_exampletable postgres pg_restore: [archiver (db)] COPY failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. WARNING: errors ignored on restore: 1 It will work if I use COPY but the table is so big I need to use pg_dump to compress it. Any help would be great. Thanks Rebecca
[GENERAL] Pg_dump Query
Hi I want to pg_dump only a select amount of rows from a table and restore them to a table on another server. Is there away to do this? I thought of using a view but how would I upload that into a specific table. Copy? or is there something I can do with pg_restore. Rebecca
[GENERAL] List Functions and Code
Hi I want to search and list all the functions in a database that reference a particular table within its code. Is there a way to do this? I can list all the functions from pg_proc, however there is nothing there which provides the code of the function, so therefore I can't query if it mentions a table. I've tried looking in information_schema.routines but this unfortunately does not have it either. Rebecca
Re: [GENERAL] Error creating function
Hi Tom Thanks for your reply. I was restoring one schema (with data) at a time from 8.3 db to a pre-created empty 8.4 db. I received a lot of errors which game down to me not restoring the public schema first. I also realized, I had not created plpgsql language for the 8.4 db before I did the restoration. So after I created the language and restarted the restoration from scratch with the public schema first, I no longer received this error. Thanks Rebecca On Wed, Jul 20, 2011 at 3:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rebecca Clarke rebe...@clarke.net.nz writes: I'm doing a restore into Postgresql 8.4, postgis 1.5.1 and I'm getting the following error when it creates the below function: pg_restore: creating FUNCTION _get_buffer(geometry, double precision, integer) pg_restore: [archiver (db)] Error from TOC entry 966; 1255 49162661 FUNCTION _get_buffer(geometry, double precision, integer) postgres pg_restore: [archiver (db)] could not execute query: ERROR: SQL function cannot accept shell type geometry Command was: CREATE FUNCTION _get_buffer(_geom geometry, _radius double precision, _density integer, OUT the_geom geometry) RETURNS geome... I don't understand what 'cannot accept shell type geometry' means? Can anyone help my lack of knowledge? Hmm, that is interesting. A shell type is a SQL base data type that has been declared to the database but its properties are not yet filled in. This is needed because the properties include I/O functions, which have to be declared to take or return the data type, so there's a circularity involved. The standard solution is CREATE TYPE typename; -- this creates typename as a shell CREATE FUNCTION typename_in(cstring) RETURNS typename ... CREATE FUNCTION typename_out(typename) RETURNS cstring ... CREATE TYPE typename (input = typename_in, output = typename_out, ...); The last step changes the type from a shell into a real, usable datatype. So what you've apparently got is a situation where that last step got missed for the geometry type, or else the _get_buffer function somehow got inserted into the middle of this sequence. I've not heard of that happening to people before, so I wonder if you could provide the exact step-by-step of what you did. regards, tom lane
[GENERAL] Error creating function
Hi I'm doing a restore into Postgresql 8.4, postgis 1.5.1 and I'm getting the following error when it creates the below function: pg_restore: creating FUNCTION _get_buffer(geometry, double precision, integer) pg_restore: [archiver (db)] Error from TOC entry 966; 1255 49162661 FUNCTION _get_buffer(geometry, double precision, integer) postgres pg_restore: [archiver (db)] could not execute query: ERROR: SQL function cannot accept shell type geometry Command was: CREATE FUNCTION _get_buffer(_geom geometry, _radius double precision, _density integer, OUT the_geom geometry) RETURNS geome... I don't understand what 'cannot accept shell type geometry' means? Can anyone help my lack of knowledge? Thanks Rebecca
[GENERAL] Database Restore Fail - No liblwgeom.so
Hi there I'm transferring a database from 8.2 to 8.4 and I have some triggers that reference liblwgeom.so within the database. When I restore a dump of the 8.2 database into my 8.4 database it says that liblwgeom.so does not exist. From various forums on the internet I have discovered that this has been replaced with the file postgis-1.5.so. Does anyone have any recommendations on how I should proceed? I am really aiming for the pg_restore to restore everything to the same state as the 8.2 database but seeing these trigger creations are erroring during the restoration they are not being generated and I want to avoid having to go in after the restore and generate them myself. Many thanks in advance. Rebecca
