Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

pg_class returns 0 as relpages

Amritpal

I have this table:

                  Table "public.region"
   Column    |   Type   | Collation | Nullable | Default 
-------------+----------+-----------+----------+---------
 r_regionkey | smallint |           | not null | 
 r_name      | text     |           |          | 
 r_comment   | text     |           |          | 
Indexes:
    "region_pkey" PRIMARY KEY, btree (r_regionkey)
Referenced by:
    TABLE "nation" CONSTRAINT "nation_n_regionkey_fkey" FOREIGN KEY (n_regionkey) REFERENCES region(r_regionkey)

weirdly this query:

select oid::regclass as tbl, relpages
from pg_class
where relname='region'

returns relpages = 0.

Why and how can I fix? If I change smallint to int it works (relpages = 1)

Demir last edited by

Your table is so small that autoanalyze never got triggered. Since autovacuum_analyze_threshold is 50 by default, tables with less than 50 data modifications are not automatically analyzed.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

S

function return multiple json rows
SQL, Database Testing • json stored procedure postgresql 9.4 • • shizuka

2

0
Votes

2
Posts

0
Views

Z

You need to also return multiple rows, not just a single value. You also don't need PL/pgSQL for this. A simple SQL function will do: create or replace function myfunc() returns table (document json) as $body$ select row_to_json(myview) from myview; $body$ language sql; returns table is the same as returns setof but you can define the column names. You also need to put the function call into the from clause: select * from myfunc(); You don't really need function for that in the first place. You could do that in the view directly: create view myview as select row_to_json(t) as document from ( select retailerid, retailername from mytable )t ;
D

How to return some * data with time interval?
SQL, Database Testing • mysql • • derk

2

0
Votes

2
Posts

0
Views

Maybe a combination of the functions SECOND() and MOD() will help. Using your test data as follows (dbfiddle here, also tested with MySQL 5.7): create table t_ ( id_ int primary key , date_ datetime ); insert into t_ values (1,'2017-07-10 10:11:10'),(2,'2017-07-10 10:11:15'), (3,'2017-07-10 10:11:20'),(4,'2017-07-10 10:11:25'), (5,'2017-07-10 10:11:30'),(6,'2017-07-10 10:11:35'), (7,'2017-07-10 10:11:40'); We can get the "second" value of the date_, for every 15th second, like so: select second(date_) from t_ where mod( second( date_ ), 15) = 0 ; -- output +---------------+ | second(date_) | +---------------+ | 15 | | 30 | +---------------+ The SECOND() value of the "last registered date" would be ... select second( max( date_) ) from t_ ; -- output +-----------------------+ | second( max( date_) ) | +-----------------------+ | 40 | +-----------------------+ Take the "last registered date" into account: select * from t_ where mod ( ( select 60 - second( max(date_) ) from t_ ) + second( date_ ) , 15 ) = 0 ; -- (your required) output +-----+---------------------+ | id_ | date_ | +-----+---------------------+ | 1 | 2017-07-10 10:11:10 | | 4 | 2017-07-10 10:11:25 | | 7 | 2017-07-10 10:11:40 | +-----+---------------------+ This query should also work if there are "gaps" in the date_ (the MOD() function does not care) eg if we DELETE everything from the table t_ and insert the following rows: -- IDs 5 (10:11:20) and 11 (10:11:50) missing insert into t_ values (1,'2017-07-10 10:11:00'), (2,'2017-07-10 10:11:05'), (3,'2017-07-10 10:11:10'), (4,'2017-07-10 10:11:15'), (6,'2017-07-10 10:11:25'), (7,'2017-07-10 10:11:30'), (8,'2017-07-10 10:11:35'), (9,'2017-07-10 10:11:40'), (10,'2017-07-10 10:11:45'), (12,'2017-07-10 10:11:55'), (13,'2017-07-10 10:12:00'),(14,'2017-07-10 10:12:05'); Our query will return: mysql> select * -> from t_ -> where mod ( -> ( select 60 - second( max(date_) ) from t_ ) -> + second( date_ ) -> , 15 -> ) = 0 ; +-----+---------------------+ | id_ | date_ | +-----+---------------------+ | 2 | 2017-07-10 10:11:05 | | 8 | 2017-07-10 10:11:35 | | 14 | 2017-07-10 10:12:05 | +-----+---------------------+ If there are (date/time) duplicates, then ... you will see them in the output. You can add a BETWEEN clause (as @Jess suggested) at the end of the query, if you need a "master interval". UPDATE It seems that you also want to capture values that are smaller/greater than the (exact multiples of) 15 seconds - you have mentioned 18,32, and 57 (seconds), respectively. With a slightly modified query, you can pick up these values, too. Eg the following query has a "tolerance" of +/- 3 seconds eg if the "exact" second is 15, we'd also get 12,13,14,16,17,18. Notice that this "frame" moves according to the MAX() value ie your "last registered" time. select * from t_ where mod ( ( select 60 - second( max(date_) ) from t_ ) + second( date_ ) , 15 ) in (0,1,2,3,12,13,14) ; 2 sets of test data (dbfiddle): "last registered" near the full minute , "last registered" at the 30 second mark
T

Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup
SQL, Database Testing • mysql ubuntu backup mysql 8.0 perc languageona tools • • Trenton

2

0
Votes

2
Posts

0
Views

E

Xtrabackup 8.0.12 should work with MySQL 8.0.20 Percona xtrabackup 8.0.12 release Percona XtraBackup 8.0.12 now supports backup and restore processing for all versions of MySQL up to and including 8.0.20; previous versions of Percona XtraBackup will not work with MySQL 8.0.20 and higher.
Search with more than 5 Million recods With Posgres
SQL, Database Testing • postgresql index full text searc languageh filtered index • • Demir

2

0
Votes

2
Posts

0
Views

O

When you need 1M records out of 5M, no index will be of use, better scan the full table.
M

Postgres jsonpath ANY Equivalent
SQL, Database Testing • postgresql • • magpie

2

0
Votes

2
Posts

0
Views

I don't know of any way to do that with jsonpath. I'd suggest pulling apartments with any number of rooms and filtering that list, but I suspect that would get quite large as the dataset grows ... ... but then, if you're talking about large data volumes here, I would have to ask why you're not doing this is a proper, Relational way? Yes, you can get postgres to do this with JSON data but should you? This is a very simple data structure that you could represent in a single table which, properly indexed, will cope with huge data volumes. select level, no, size, rooms from apts order by 1, 2 ; +-------+----+------+-------+ | level | no | size | rooms | +-------+----+------+-------+ | 1 | 1 | 40 | 1 | | 1 | 2 | 80 | 3 | | 1 | 3 | null | 2 | | 2 | 4 | 100 | 3 | | 2 | 4 | 60 | 2 | +-------+----+------+-------+ select * from apts where rooms in ( 3, 5, 6, 10, 11 ) ; +-------+----+------+-------+ | level | no | size | rooms | +-------+----+------+-------+ | 1 | 2 | 80 | 3 | | 2 | 4 | 100 | 3 | +-------+----+------+-------+ Relational databases are really good at finding small bits of [related] data and bolting them back together again. They're generally pretty rubbish at taking large blocks of data, like chunks of JSON, and pulling them apart again. Yes, RDBMS providers are trying to improve this, but I would suggest it'll still be way slower than doing it the "old-fashioned" way.
C

returning a null value instead of no value
SQL, Database Testing • sql server syntax • • charlenef

2

0
Votes

2
Posts

0
Views

N

Your last 2 SELECTs are without a FROM clause. These will always return a single row of scalar values. The fact that the subquery did not return any rows, caused the expression to be NULL - unknown. Think about the following statement - how would you expect the engine to handle this: SELECT 1 AS FirstCol, () AS SecondCol Note: This is a non standard T-SQL extension. In Oracle for example, you need to add the FROM DUAL or you will get ORA-00923: FROM keyword not found where expected. However, this same statement with FROM DUAL added run in Oracle will also result in a single row of scalar values.
Why CASE WHEN always return 0 and 1 in mysql?
SQL, Database Testing • mysql • • briley

2

0
Votes

2
Posts

1
Views

E

Everything is running as programmed. So for right_score = 0 WHEN right_score < 3 THEN right_score = 1 First MySQL checks if right_score is smaller than three and in the next and second step MySQL checks if right_score is equal to 1 what is false which mysql tells by sending a 0 So in that line you have two comparisons and the result is what you have programmed a bunch of true(1) and false (0) If you remove all the second comparisons, you get what you expected. CREATE TABLE de_data(id varchar(6),right_score int , left_score int) INSERT INTO de_data VALUES ('037629', 1,1),('037629', 3,1),('037629', 1,3),('037629', 3,3),('037629', 2,2) ,('037629', 1,1),('037629', 2,2) SELECT right_score, left_score, CASE WHEN right_score < 3 THEN right_score = 1 WHEN right_score >= 3 THEN right_score = 0 END AS r_result, CASE WHEN left_score < 3 THEN left_score = 1 WHEN left_score >= 3 THEN left_score = 0 END AS l_result FROM de_data WHERE id = 037629 right_score | left_score | r_result | l_result ----------: | ---------: | -------: | -------: 1 | 1 | 1 | 1 3 | 1 | 0 | 1 1 | 3 | 1 | 0 3 | 3 | 0 | 0 2 | 2 | 0 | 0 1 | 1 | 1 | 1 2 | 2 | 0 | 0 SELECT right_score, left_score, CASE WHEN right_score < 3 THEN 1 WHEN right_score >= 3 THEN 0 END AS r_result, CASE WHEN left_score < 3 THEN 1 WHEN left_score >= 3 THEN 0 END AS l_result FROM de_data WHERE id = 037629 right_score | left_score | r_result | l_result ----------: | ---------: | -------: | -------: 1 | 1 | 1 | 1 3 | 1 | 0 | 1 1 | 3 | 1 | 0 3 | 3 | 0 | 0 2 | 2 | 1 | 1 1 | 1 | 1 | 1 2 | 2 | 1 | 1 db<>fiddle here
Is Postgres password-based authentication secure?
Security, Penetration Testing • postgresql tls databases md5 • • Alberto

2

0
Votes

2
Posts

0
Views

F

If using SSL, then what PostgreSQL does is fine. If not using SSL, but still doing the authentication across the network, then what PostgreSQL does stinks. Their games with MD5 are worthless, but not because they use MD5. MD5 has its own issues, but there they are just misusing it awfully. With "cleartext password" authentication, the client shows a user name and a password, and the server accepts them if they match what the server stored. With "md5" authentication, the client shows a value (which happens to be the MD5 hash of the concatenation of the password and the user name) and the server accepts it if it matches what the server stored. So you see it: in both cases, the client shows a bunch of bytes to the server, always the same sequence. It suffices for an attacker to tap on the network to observe these bytes, and then connect to the server and sends the same bytes to be granted entry. That the bytes are the result of a MD5 hash is completely irrelevant here. This MD5 hash is said to be password equivalent. As long as the connection can be eavesdropped at all (i.e. no SSL), then security goes down the drain. See this page for the details of the MD5 computation. They call it "encryption", which it is not at all (hashing is not encryption). We could even argue that using this MD5 decreases security: when a plaintext password is used, it can be forwarded to another authentication server (using Kerberos, LDAP,...) and that authentication server could then employ strong storage techniques (see password hashing). With the PostgreSQL-specific MD5 hash, this cannot apply. When the "md5" authentication method is used, it MUST be against a table in the database which contains all these "md5" value as is. An attacker who can get his hands on, say, a backup tape or an old disk, will immediately obtain many free accounts on the database. And, I insist, nothing in all this has anything to do with MD5 cryptographic weaknesses. All of this would still apply if MD5 was replaced with SHA-512.
Missing data for one schema in pg_dump
SQL, Database Testing • postgresql backup postgresql 9.6 pg dump • • Demir

2

0
Votes

2
Posts

0
Views

P

If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers. You should verify what the database user used by pg_dump sees when selecting from the original table. The only explanations I can think of are: Somebody deleted all rows from the table. You have experienced data corruption that somehow rendered the table's file empty of zeroed out. Try the following to find the file: SELECT oid FROM pg_database WHERE datname = 'db'; SELECT relfilenode FROM pg_class WHERE oid = 'schema1.some_table'::regclass; If the numbers returned are 1234 and 5678, examine the file base/1234/5678 in the data directory: How big is it, what does it contain?
C

Error when trying to make UPDATE with Angular and Nodejs/express
Software Programming • postgresql angular nodejs expresses • • charlenef

2

0
Votes

2
Posts

0
Views

C

The problem is on the express side. You have to send a json and yet you're sending a string.You got it.res.json(`El libro con id ${req.params.id} fue dado de baja exitosamente!`); When it should beres.json({status: 'ok', message: `El libro con id ${req.params.id} fue dado de baja exitosamente!`}); Same in you. catch. .json("error al intentar dar de baja el libro");
M

Ideal way to return a scalar from function (RETURN vs. SELECT)?
SQL, Database Testing • postgresql functions • • magpie

2

0
Votes

2
Posts

0
Views

B

I'd prefer to use RETURN rather than RETURN QUERY SELECT in pl/pgsql. But there's no need to jump through hoops. The cost of returning results with SELECT is negligible compared to other costs in pl/pgsql procedures. Everything is a result-set in PostgreSQL anyway, even a single scalar, so you're not saving much with RETURN.
C

Postgresql - Error in function that returns table
SQL, Database Testing • postgresql plpgsql • • charlenef

2

0
Votes

2
Posts

0
Views

A

You don't need plpgsql for this. A plain sql function will do: create or replace function GET_COLUMNS(in tbl_name character varying(30)) returns table(colunas character varying) as $$ SELECT column_name FROM information_schema.columns WHERE table_schema = 'Main' AND table_name = tbl_name; $$ language sql; Additionally: the language name is an identifier. Don't put it in single quotes.
T

Issues starting Postgres server
SQL, Database Testing • postgresql • • terrea

2

0
Votes

2
Posts

0
Views

A

Per JimmyStenke's suggestion, I realized I had a DB initialized from a previous install, and it seems that was causing the error. I deleted it and initialized a new DB.
T

How to tell if a project needs a NoSQL database solution?
SQL, Database Testing • mysql postgresql mongodb nosql • • tahishae

2

0
Votes

2
Posts

0
Views

C

How to tell if a project needs NoSQL database solution? I think that SQL limits define it, if project needs bigger database or bigger fields than it needs NoSQL. Am I right? You are not right Choosing NoSQL over a relational database has a lot more to do with business reasons like: do we have people on the team already familiar with NoSQL database? will it be cheaper (in terms of hardware, licensing, support, etc)? will it be faster (because of tooling, or the schema-less nature of document databases, etc)? It also has a lot to do with the types of data you're storing, and your data modeling requirements. For instance, if your application needs to store large amounts of XML or JSON data, or if every entity you save might be slightly different, you might be better off with NoSQL. But if you need to model relationships and enforce constraints, referential integrity, etc, then you would likely be better off with a relational DB.
Why are records from my data-modifying CTE not appearing in the subsequent SELECT from the insertion table?
SQL, Database Testing • postgresql • • irl

2

0
Votes

2
Posts

0
Views

T

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. (... ) and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. (emphasis mine) So you need a RETURNING clause in your merge_op clause to see the inserted rows. -- merge query WITH incoming (name, value) AS ( VALUES ('asdf','null'::JSONB), ('asdf', '18446744073709551615'::JSONB), ('asdaf', '3'::JSONB) ) , merge_op AS ( INSERT INTO target_table(name, value) SELECT i.name, i.value FROM incoming i LEFT JOIN target_table er ON er.name = i.name AND er.value = i.value WHERE er.name IS NULL ON CONFLICT DO NOTHING returning * ) SELECT er.id, er.name, er.value FROM target_table er JOIN incoming i ON i.name = er.name AND i.value = er.value; If you also want to see the rows from incoming that were not inserted (e.g. due to a conflict) you need an outer join: SELECT er.id, i.name, i.value FROM incoming i LEFT JOIN target_table er ON i.name = er.name AND i.value = er.value;

pg_class returns 0 as relpages

Suggested Topics

function return multiple json rows SQL, Database Testing • json stored procedure postgresql 9.4 • • shizuka

How to return some * data with time interval? SQL, Database Testing • mysql • • derk

Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup SQL, Database Testing • mysql ubuntu backup mysql 8.0 perc languageona tools • • Trenton

Search with more than 5 Million recods With Posgres SQL, Database Testing • postgresql index full text searc languageh filtered index • • Demir

Postgres jsonpath ANY Equivalent SQL, Database Testing • postgresql • • magpie

returning a null value instead of no value SQL, Database Testing • sql server syntax • • charlenef

Why CASE WHEN always return 0 and 1 in mysql? SQL, Database Testing • mysql • • briley

Is Postgres password-based authentication secure? Security, Penetration Testing • postgresql tls databases md5 • • Alberto

Missing data for one schema in pg_dump SQL, Database Testing • postgresql backup postgresql 9.6 pg dump • • Demir

Error when trying to make UPDATE with Angular and Nodejs/express Software Programming • postgresql angular nodejs expresses • • charlenef

Ideal way to return a scalar from function (RETURN vs. SELECT)? SQL, Database Testing • postgresql functions • • magpie

Postgresql - Error in function that returns table SQL, Database Testing • postgresql plpgsql • • charlenef

Issues starting Postgres server SQL, Database Testing • postgresql • • terrea

How to tell if a project needs a NoSQL database solution? SQL, Database Testing • mysql postgresql mongodb nosql • • tahishae

Why are records from my data-modifying CTE not appearing in the subsequent SELECT from the insertion table? SQL, Database Testing • postgresql • • irl

function return multiple json rows
SQL, Database Testing • json stored procedure postgresql 9.4 • • shizuka

How to return some * data with time interval?
SQL, Database Testing • mysql • • derk

Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup
SQL, Database Testing • mysql ubuntu backup mysql 8.0 perc languageona tools • • Trenton

Search with more than 5 Million recods With Posgres
SQL, Database Testing • postgresql index full text searc languageh filtered index • • Demir

Postgres jsonpath ANY Equivalent
SQL, Database Testing • postgresql • • magpie

returning a null value instead of no value
SQL, Database Testing • sql server syntax • • charlenef

Why CASE WHEN always return 0 and 1 in mysql?
SQL, Database Testing • mysql • • briley

Is Postgres password-based authentication secure?
Security, Penetration Testing • postgresql tls databases md5 • • Alberto

Missing data for one schema in pg_dump
SQL, Database Testing • postgresql backup postgresql 9.6 pg dump • • Demir

Error when trying to make UPDATE with Angular and Nodejs/express
Software Programming • postgresql angular nodejs expresses • • charlenef

Ideal way to return a scalar from function (RETURN vs. SELECT)?
SQL, Database Testing • postgresql functions • • magpie

Postgresql - Error in function that returns table
SQL, Database Testing • postgresql plpgsql • • charlenef

Issues starting Postgres server
SQL, Database Testing • postgresql • • terrea

How to tell if a project needs a NoSQL database solution?
SQL, Database Testing • mysql postgresql mongodb nosql • • tahishae

Why are records from my data-modifying CTE not appearing in the subsequent SELECT from the insertion table?
SQL, Database Testing • postgresql • • irl