Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any new columns are added to a table so we have to drop the table, due to which the specific table access for the user is gone. Access to a specific table is given through GRANT. Can you guys suggest us a way in which specific table access can be given, and will remain if the table is dropped and recreated?

Is instant text search possible in big table with HTMLs?

I have a pretty big table with HTMLs (about 500K rows, average length of text is ~150K). It is required to make fast search (less than 1s) rows which match let say ‘%<meta name="twitter:app%’. First 10 results are enough.

Possible solution I have tired (on Postgres mainly):

  1. Full-text search: results look not relevant and may miss something (can not find a way to improve it).
  2. Trigram index (...USING GIN (t gin_trgm_ops)...) – a cool feature of PosgreSQL by the way. It worked fast on my synthetic tests, but when I applied to my HTMLs set – it worked minutes(!). I used explain anaylze, what showed me that index is used (what is nice), but than Postgres recheck what matched by index and this is quite slow, because it needs to make linear text search in large texts, one by one. On synthetic tests it worked because my texts were relatively small.
  3. Also I tied Elastic (I used ‘wildcard field type’, Elastic use trigram index as well in that case), but performance was even worse that in Postgres case.

I still believe that it possible, but how?

Flexible table columns width, rows independent?

Hello,

i have HTML table with 3 rows.
the third row contains in the middle three columns that contains extraordinary long phrasesphrases which cause columns above these long columns to be long too and that looks bad, because above rows contains images so spacing is different.

How can i set the HTML table columns width to be flexible, i mean so the wide third row columns does not influence width of the columns in other rows of the same table?

Flexible table columns width, rows independent?

using a column that references an object_id in your own table

Is it safe to use OBJECT_ID() as a reference in your own tables, with the assumption that the value will always refer to the same database object no matter what?

The msdn states

All metadata functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

so that would indicate it’s not safe, but I couldn’t find any (other) hard facts that it shouldn’t be done, and, to be fair, no examples of people actually doing it, which in itself might be evidence not to do it.

But the value OBJECT_ID() is (or seems to be) used in the system tables and views as keys from one object to another, so there must be some form of integrity and reliability.

For the record: I don’t necessarily need it, but it just popped into my mind as an option while designing my system, and got curious.

MSSQL – Incapacity to query underlying table of a schemabound view

I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both enterprise edition) using backup-restore technique and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE STATISTICS XXX

At statistics update, I get following error:

Msg 402, Level 16, State 1, Procedure ZZZZ, Line 5 [Batch Start Line 0] The data types datetime and time are incompatible in the add operator. Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'ZZZZ' because of binding errors. 

I know that the message is quite explicit (the view, which was syntactically correct in MSSQL 2008R2 is not anymore in MSSQL 2019) but I don’t understand why would a view defined WITH SCHEMABINDING that is not valid would prevent the update of statistics on an underlying table.

Moreover, I get the same error message when querying the underlying table with a WHERE clause, except if I force a FULLSCAN with following hint:

OPTION(TABLE HINT( $ mytable, FORCESCAN ))

I know SQL Server will fire an error in case of DDL change to underlying schema-bound objects, but I don’t understand why, if the view is invalid, the underlying objects cannot be used as usual. I’m sure there is an explanation (like the protection mechanisms for schema-bound views cannot be processed due to the invalidity of the view), but I can’t find it for sure in MS documentation…

How does Arcane Armor’s removal of Strength requirements apply at a table using the Encumbrance variant?

The Armorer Artificer’s Arcane Armor feature says:

If the armor normally has a Strength requirement, the arcane armor lacks this requirement for you.

This is in reference to the following regarding certain heavy armors:

If the Armor table shows "Str 13" or "Str 15" in the Strength column for an armor type, the armor reduces the wearer’s speed by 10 feet unless the wearer has a Strength score equal to or higher than the listed score. [PHB, pg 144]

However, the Encumbrance variant says, in part:

When you use this variant, ignore the Strength column of the Armor table in chapter 5.

If you carry weight in excess of 5 times your Strength score, you are encumbered, which means your speed drops by 10 feet. [PHB, pg 176]

Is there guidance for how to apply the Arcane Armor feature at a table that uses the Encumbrance variant?

How to predict the next value in a table? [closed]

I have a table full of values. I want to know what the next values may be.

PostgreSQL cannot look into the future, but it can guess based on existing data.

I will not install any software or extensions.

I have not hooked this script up to a syringe full of poison which sticks me if it predicts the wrong number, nor will this mechanism be auto-trading on the stock market. I will not sue anyone if it predicts the wrong number. It will be used "for novelty purposes only".

What would be the most likely "algorithm" to stand the best chance of guessing the next value?

If I just do:

SELECT avg(integer_column) FROM table; 

I will get the average number. That seems like a reasonable guess, but it also seems a bit simplistic. It seems like it could do better than that, without getting into some kind of crazy, ultra-complex query.

Why am I getting this error: Incorrect syntax for definition of the table constraint

CREATE TABLE COMPUTER_ASSIGNMENT ( SerialNumber Int NOT NULL, EmployeeNumber Int NOT NULL, DateAssigned Date NOT NULL, DateReassigned Date NULL, CONSTRAINT SerialNumberPK PRIMARY KEY (SerialNumber, EmployeeNumber), REFERENCES COMPUTER (SerialNumber), CONSTRAINT SerialNumberFK FOREIGN KEY (SerialNumber, EmployeeNumber) REFERENCES EMPLOYEE (EmployeeNumber) );

how to delete specific range of data from system queue data table?

due to sudden increase of my database .mdf file we check there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table.

we try this code:

declare @c uniqueidentifier while(1=1) begin     select top 1 @c = conversation_handle from dbo.queuename     if (@@ROWCOUNT = 0)     break     end conversation @c with cleanup end   

but its try to delete all data and due to space limitation the query cannot execute. my internal message queue table :

enter image description here

enter image description here

What is my error trying to create a new table in the database?

I tried creating the tables in different ways and I always get the same error: "Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘( …… at line 1 of the WordPress database for the CREATE TABLE IF NOT query EXISTS"

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );          global $  wpdb, $  rs_plugin_db_version, $  table_name_clientes, $  table_name_promociones;          $  charset_collate = $  wpdb->get_charset_collate();          $  wpdb->query(         "CREATE TABLE IF NOT EXISTS $  table_name_clientes (             id int(11) NOT NULL,             logo text NOT NULL,             name text NOT NULL,             whatsapp text DEFAULT '' NOT NULL,             instagram text DEFAULT '' NOT NULL,             facebook text DEFAULT '' NOT NULL,             PRIMARY KEY  (id)         ) $  charset_collate ENGINE = InnoDB"     );     dbDelta();          $  wpdb->query(         "CREATE TABLE IF NOT EXISTS $  table_name_promociones (             id int(11) NOT NULL,             img text NOT NULL,             title text NOT NULL,             content text NOT NULL,             owner int(11) NOT NULL,             contact int(11) NOT NULL,             PRIMARY KEY  (id),             FOREIGN KEY  (owner) REFERENCES $  table_name_clientes(id) ON DELETE CASCADE ON UPDATE CASCADE         ) $  charset_collate ENGINE = InnoDB"     );     dbDelta(); 

In one of the tables I need to create a foreign key, if I didn’t misunderstood dbDelta() does not support them and therefore my last attempt was with $ wpdb->query, but with or without foreign key the result is the same.

maybe my mistake is obvious, but honestly I can’t find it