20
Your description results in a table definition like this:
CREATE TABLE tbl (
lap_id serial PRIMARY KEY
, lap_no int NOT NULL
, car_type enum NOT NULL
, race_id int NOT NULL -- REFERENCES ...
, UNIQUE(race_id, car_type, lap_no)
);
General solution for this class of problems
To get the longest sequence (1 result, longest of all, arbitrary pick ...
answered Feb 25 '13 at 18:18
Erwin Brandstetter
131k17 gold badges308 silver badges453 bronze badges
7
create table tbl (lap_no int, car_type text, race_id int);
insert into tbl values (1,'red',1),(2,'red',1),(3,'red',1),(4,'red',1),
(1,'blue',1),(5,'red',1),(2,'blue',1),(1,'green',1);
select car_type, race_id, sum(case when lap_no=(prev+1) then 1 else 0 end)+1 seq_len
from ( select *, lag(lap_no) over (partition by car_type,...
5
Your plain subselect fetches up to 100 rows, but does not lock them against write access. Concurrent transactions can update or delete one or more of those rows before DELETE can lock the rows (at least with the default isolation level READ COMMITTED). This would result in your error message.
To defend against this race condition, lock the rows in the ...
answered Jan 14 '17 at 19:33
Erwin Brandstetter
131k17 gold badges308 silver badges453 bronze badges
5
Use substring() with a regular expression instead:
substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')
The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned.
Like your code, this selects the first string matching the pattern and does not ...
5
Proper crosstab query
First of all, the presented query would not work while you still have char(n) in your table definition - like we discussed under your previous question. You would need to convert to text or varchar first.
This works with your current table definition:
SELECT * FROM crosstab(
'SELECT postcode_nospace_::text, ageband::text, count(...
5
If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes.
TABLESPACE
Yes, your last idea could work. ...
4
Data model
You are saving avg. and max. speed per race, not per driver? Normally this would be something like:
CREATE TABLE race (
race_id serial PRIMARY KEY
,race_date timestamp
);
CREATE TABLE driver (
driver_id serial PRIMARY KEY
,driver_name text
,date_started timestamp
);
CREATE TABLE driver_race (
driver_id int REFERENCES driver(...
4
Your table definition says
...
ageband character(12),
...
which means the values in there look like '18-24 ' instead of '18-24'. This way, the items from the VALUES list do not match the values in the table, therefore you get an empty table as result.
If you change the column type to a bit more senseful text (which trims the values, too, as Erwin ...
3
Finally I got solution, I am just doing reindex the table, and its work properly now.
reindex table test
Thank you, all of you for your suggestion.
3
Autovacuum won't run against the table if it not changing. In your case, you are appending data to the table so autovacuum will run, but it is the ANALYZE portion that is worth mentioning. The DELETEs will kick of autovacuum runs of course.
Are you seeing performance problems when autovacuum runs? If not, I would recommend leaving it enabled. After your ...
2
Don't disable autovacuum unless you have a very good reason. At the absolute most, set per-table vacuum parameters to limit how it runs on a particular table.
Autovacuum maintains table statistics and you actually want it to run very often on append-only tables to make sure you get good query plans; that's a known problem area for statistics- based planners,...
2
You may want to try:
Separate getting the candidate rows for deletion, from Deleting them
For example:
CREATE TEMP TABLE tmp_test1 AS SELECT test_id FROM test WHERE user_id = 2;
CREATE TEMP TABLE tmp_test2 AS SELECT * FROM tmp_test1 LIMIT 100;
{Loop while tmp_test2 has rows}
DELETE FROM test WHERE test_id IN(SELECT test_id FROM tmp_test2);
DELETE FROM ...
1
To answer myself.
It was somehow related to https://www.postgresql.org/message-id/flat/111D0E27-A8F3-4A84-A4E0-B0FB703863DF%40s24.com#111D0E27-A8F3-4A84-A4E0-B0FB703863DF@s24.com i think.
The OS changed from Debian7 to Debian8, and so the dynamically linked libc6 changed.
Had to do a REINDEX on all my Tables with Indexed String Columns.
1
there is another way to reduce your bloat space when you can remove tuples in heap pages,
this method use less adding space to reduce your bloat table.
LIKE exp :
pg93@db-172-16-3-150-> psql
psql (9.3.3)
Type "help" for help.
digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp);
CREATE TABLE
digoal=# insert into t_bloat ...
1
If your environment has disk space more than this table's "real data(after reduced)" size.
you can use pg_reorg reduce this table's bloat space.
or you can use londiste3 increment copy this table, and use less time to exchange.
but if your env has no more space than this table, you can't reduce.
1
It looks like broken data file or broken indexes. If this problem grows, then it can shows some deep system problems - problems with hw (memory, CPU), os (antivirus)... Try to use a commands executed from command prompt for database access repairing a) reindexdb - refresh all indexes, b) pg_dump - recheck a readability of data files.
1
If you only use Streaming replication, it doesn't support the write operation on the slave node. Currently, PostgreSQL doesn't support multiple master itself, If you want multiple master solution, I suggest you use PostgreSQL-XC.
Only top voted, non community-wiki answers of a minimum length are eligible
